Very basic data blending dont work - what am I doing wrong?

[Bigdage]

Hello Looker community.

I have a very simple problem (I think).  Let's make it as simple as possible 

1) I have a Google Sheet  with 2 pages (one page per staff).  Each page has 3 columns (Date, Staff name, Sales amount).  

2) I have connected my 2 pages to my Looker Studio .  My challenge is simple:

3) I want to set up a connector where I can select the desired date

4) I want to set up a connector where I can select the desired staff

The problem is when I blend my data using the internal join operator (selecting date and staff for my dimension and sales for metrics) it just returns blank results for my 2 selectors instead of giving me choices

Any idea anyone of what I'm missing here?

Thanks in advance

[momoises19 · Accepted solution]

Effectively, in this case you need to join the data with a union all (vertically), so you can have the filters of both table a and table b, this union all has to be done in the google sheet because looker studio does not allow it.

[momoises19]

Hi,

It does not bring data because it is using as join the staff column, which has no matches, that is, there are no rows with the same value in sheet one with sheet 2. you should leave the join with the date column but I do not know if it is what you need.

[Bigdage]

Hello momoises19 and thank you for trying to help me out. 

Even if I only use only the date as join, my date selector DATE comes out blank (even if my dates are identical in both sheets).     What I need as a result is being able to use the selector to choose the date and another selector to choose the staff and get the according result in a graph depending of what was selected in the selector. 

[Bigdage]

What I would like at the end is being able sort my data by selecting different date and/or  different staff.  Is there any way to do so using Looker Studio?  or I need to work out my data on a new sheet (Sheet 3)  by place the data of sheet 2 at the end of sheet 1 kind of.  My column B would all info about Debbie and under all the info about Lale.  In that case my selectors would work fine 

[momoises19 · Accepted solution]

Effectively, in this case you need to join the data with a union all (vertically), so you can have the filters of both table a and table b, this union all has to be done in the google sheet because looker studio does not allow it.

[Bigdage]

So it means I need to process the blending of the 2 tables first in Google Sheet so I will then be able to use different selectors in Looker afterwards... If so, can you give me a hint of what functions I need to look for in Google Sheet to get what I want.  Thanks

[momoises19 · Accepted solution]

Of course, inside the new sheet you will find the formula that I have applied to join the data, and it only remains to connect this new sheet to the board and see the result.

This problem existed in my current work and that was the way I solved it.

I leave the link of the function for your reference: https://support.google.com/docs/answer/3093343?hl=en