Use parameters across different ids in 2 tables

[Prajakta]

I have 2 datasets (one of it is a consolidation of multiple tables)-LeadsDm and Messages

Leads is joined to messages by lead_id. Here is the design of the table. Please keep in mind that I cannot join the LeadsDM and messages table as the data is about 65 M rows.

The leads table has a username (store name) which is not present in the message table. I need to get it in the message table as well so that I can identify messages for each Username(store). But I do not want to join those tables due to server timeout restrictions.

How can I check the same leads present in the messages table through a parameter? I would apply a parameter on lead_id.

Here is the layout of the dummy table.

This is the lead_id from message table

And this is the lead_id from leadsDM table.

How can i get only the lead_ids from LeadsDM table to show up in the messages table as well?
Thank you!

[apjvinod]

Hi @Prajakta

Are you getting server timeout issue , when running QuickSight SPICE refresh when joining messages & leads table and whats the underlying datasource ?

one alternative approach is to create multiple datasets and then do the join in QuickSight SPICE

1. create one dataset for (leads, customers, accounts) tables joined and enable SPICE refresh
2. create second dataset with message table and add the first created dataset and establish the join .
   
   

In addition please look into the below post for sample approach to implement the dynamic lookup in QuickSight.

Quicksight - Dynamic function lookup id

Posting customer question (submitted by awn1995 via stack overflow) and couple of options to accomplish similar outcome. Is it possible to use another function or a parameter in place of the absolute value in a lookup ID? I have a table of data that looks like this: Date Value 2022-01-01 1,264 2021-12-31 1,232 2021-12-30 1,221 2021-12-29 1,121 I’ve created two parameters to store date values, one called “latestdate” which just carries the latest date value (top of Date column…

[Prajakta]

Hi @apjvinod ,
O have created a dataset for leadDM (leads+account+customers) and messages

I do not want to join those 2 datasets(messages and leadDM because then I will lose the date at which leads were submitted and messages were submitted (because I just want to create one date filter so that the users can control the date in a better way)
Like for example- lead 1234 was submitted on July 3rd. Message was Sent on July 4th.
Also, the messages dataset has 34 million rows and the leads has 1 million. And I’m getting an error when I’m joining those 2 datasets.

I do have lead_id present in both, leads and messages. But I do not have username present in messages.
Right now, I’m creating a parameter called LeadPM in the analysis. But that parameter in unable to fetch the leads present in messages for particular username and date range. Any fixes for that?

parameter creation- I have dynamically taken only those values which have lead ids for a particular username.

p

Leads table

Messages table-

In the messages table, i have added a control filter on the lead_id where is would select lead_id from the parameter.

And I have added a control for the parameter on the top of the sheet as well.

The main problem is that the message table is not getting filtered according to the lead_ids from the lead dataset based on the parameter. The parameter control does not give me an option select all the lead_ids present in the filter, I have to search for them instead.
How can I fix this?

[andres007]

I think your problem might be that you are using the parameter from the same dataset that you are filtering, therefore you will not see any parameters.

Can you try creating a new dataset with a custom sql something like:

select distinct leadpm from your_table

And use that dataset for the parameter?

[Prajakta]

@andres007 I don’t completely understand what you are trying to say. But while creating a new parameter, I do not want to apply that on the dataset level in the where clause or select clause.

For now, I have 2 datasets- messages and leads and I have them both in one analysis. And I’m creating a parameter in the same analysis (so it’s don’t really matter which dataset I’m creating on right as it is not on the dataset level but the analysis level).
I’m trying to filter out lead_ids in messages table based on the Username and Date in the leads table

[andres007]

I am trying to understand the problem but it is hard for me going through the thread. Please tell me if I am right with what I understand.

You want to create a parameter that has a specific list of defaults depending on the quicksight username, which you are defining with the dynamic default using the Leads dataset using the Dashboard Username so that this user only sees the specific lead_id that correspond to him in the selection LeadParameter.

Then when you use this parameter as a filter for the same dataset you do not see any results in the parameter.

Question, what do you mean by “The parameter control does not give me an option select all the lead_ids present in the filter, I have to search for them instead.”

When you confirm these assumptions I will try to make a mock of the case and see what could be going wrong but without understanding the problem it is very for me to advise in a solution.

It might be that we are not able to solve this with a message exchange on community, if we cannot solve this soon, we will take another approach so we work together with your account Solutions Architect to understand and solve your problem

Kind regards,
Andres.

[Prajakta]

Hi @andres007 , I did talk to a quicksight solutions architect and the problem was the my dataset had too many unique values which the control cannot show and filter at once. My guess is that a control can accept only 1000 unique values while showing a filter list.