Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Create table from Data Load Editor based in conditional selections of previous tables

I will simplify my question to understand it easier: I have a table called "Stores" defined by the following Qlik Code in Data Load Editor:

Stores:

Load * inline

[

id, sales, id_session, coupon

1a, 3, 1, true

2a, 1, 1, true

3a, 2, 1, false

1b, 3, 2, true

1c, 4, 3, false

];

Graphically:

idsalesid_sessioncoupon
1a31true
2a11true
3a21false
1b32true
1c43false

I have a second table called "Sessions" loaded in Qlik through my code:

Sessions:

Load * inline

[

id_session, sum_sales,

1, 6,

2, 3,

3, 4,

];

Graphically:

id_sessionsum_sales
16
23
34

And what I want to create is a new table called Results that is a modification of table "Sessions" where the column "sum_sales" is replaced by new 2 columns ("sum_with_coupon" and "sum_without_coupon") whose value depends on the value true or false of the column "coupon" of table "Stores".  This means that the sum of "sum_with_coupon" and "sum_without_coupon" in each row of table "Results" (id_session=X) must match the value of Sum(Sales) where id_session = X  in table "Stores". I know how to do it in a table generated through "Table" in Qlik Sense charts and adapting the metric. What I would like to do is generating the table in the Data Load Editor, pior to working with it through visualzations menu. Thanks in advance for your help. Anibal

id_sessionsum_with_couponsum_without_coupon
142
230
304
1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

Maybe something like this ;

Stores:

Load * inline

[

id, sales, id_session, coupon

1a, 3, 1, true

2a, 1, 1, true

3a, 2, 1, false

1b, 3, 2, true

1c, 4, 3, false

];

Results:

Load

id_session,

sum ( sales_with_coupon ) as sales_with_coupon ,

sum ( sales_without_coupon ) as sales_without_coupon

group by id_session

;

Load

id_session,

if ( coupon = 'true' , sales ) as sales_with_coupon ,

if ( coupon = 'false' , sales ) as sales_without_coupon

resident Stores

;

Drop Table Stores ;

View solution in original post

4 Replies
devarasu07
Master II
Master II

Hi,

Try to create table using below set analysis expression you can able to achieve it.

dimension: id_session

measure:1 with coupon

sum( {$<coupon={'true'}>}sales)

measure:2 without coupon

sum( {$<coupon={'false'}>}sales)

P.S: refer to the attached basics of set analysis and it might useful for you

Ignore filters in tables*

Thanks,
Deva

Anonymous
Not applicable
Author

Hi Devarasu R,

Thank you very much for your response, nevertheless it is not what I am looking for. I want to create the new table through the Data Load Editor. With this response I would not know how to create the table with the 3 fields mentioned using for example "Resident" option to make good use of the tables already loaded. Thanks in advance.

Anonymous
Not applicable
Author

Maybe something like this ;

Stores:

Load * inline

[

id, sales, id_session, coupon

1a, 3, 1, true

2a, 1, 1, true

3a, 2, 1, false

1b, 3, 2, true

1c, 4, 3, false

];

Results:

Load

id_session,

sum ( sales_with_coupon ) as sales_with_coupon ,

sum ( sales_without_coupon ) as sales_without_coupon

group by id_session

;

Load

id_session,

if ( coupon = 'true' , sales ) as sales_with_coupon ,

if ( coupon = 'false' , sales ) as sales_without_coupon

resident Stores

;

Drop Table Stores ;

Anonymous
Not applicable
Author

Excellent @Bill Markham. With your response I see that in fact "Sessions" table isn´t actually needed as all the information required to build "Results" is stored already in "Stores". Thanks.