Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
niskapadia
New Contributor

Select table based on query

Hello Experts,

I have aggregate table and Detail table in our data warehouse. My requirement is if user selects columns which exist in Aggregate table, query should get it from Aggregate table, if column doesn't exist in Aggregate table then it should come from Detail table. 

I have used Business Objects in the past, we could do this using Aggregate Aware functionality but not sure how to achieve this in Qlik. Any help would be greatly appreciated.

Thank you..

Labels (2)
6 Replies
Digvijay_Singh
Honored Contributor III

Re: Select table based on query

I think the relevant concept is explained here, haven't used it though - 

https://help.qlik.com/en-US/sense/September2019/Subsystems/Hub/Content/Sense_Hub/DataSource/Manage-b...

vunguyenq89
Contributor III

Re: Select table based on query

Hi, 

You can use system variable ErrorMode to implement an exception handling:

  • First try querying the Aggregate table
  • If this query fails (column doesn't exist) then query the Detail table

Sample script:

SET ErrorMode = 0; 
SELECT columns FROM Aggregate;
IF ScriptError >0 then 
     SET ErrorMode = 1; 
     SELECT columns FROM Detail;
ENDIF
SET ErrorMode = 1;

Hope this helps.

BR,

Vu Nguyen

niskapadia
New Contributor

Re: Select table based on query

Thank you so much.. Here is the issue.. 

I added script like this..

SET ErrorMode = 0;
SET ScriptError=0;
SELECT distinct CUST_NAME,
CUST_ADDR
FROM AGGREGATE;

IF ScriptError >0 then
SET ErrorMode = 1;
SELECT CUST_NAME, CUST_ADDRESS, PH_NBR  FROM DETAIL
ENDIF
SET ErrorMode = 1;

I am able to load the script fine but don't see the column PH_NBR in my app from Detail Query. Do you know if I have done anything wrong in this query?

Thank you,

Ni**bleep**h

Channa
Valued Contributor III

Re: Select table based on query

this one is little different

example

you have 2 tables

year,sales_Y

month,sales_M

 

he create one measure as sales

if he use year as dimension it should bring sales_y , if he use Month it should take sales_M

 

 

Channa
niskapadia
New Contributor

Re: Select table based on query

Okay so how do you control the mapping. When user select Year and Sales measure, it goes to year table. Where do you manage this?

Channa
Valued Contributor III

Re: Select table based on query

it is not possible

or you do union of both tables

Channa