Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for
Did you mean:
Contributor II

Set analysis problem

Hello,

Please I need your help, I have a table:

tab:
load* inline [ customer,product, unitprice
astrida,aa,15
astrida,aa,16
astrida,bb,9
betacap,bb,10
betacap,bb,12
betacap,cc,15
]

i want to show a new table (using set analysis or if clause):

Totals          sum(unitprice)=77 , 6

Astrida, aa,                 31        ,1

Astrida,bb,                   9         ,1

betacap,bb,                22       ,1

betacap,cc,                 15       ,1

where the new column is contains 1 for each combination of (customer,product) and 6 as total of records from table tab.

Thank you

Arianna

Labels (1)
• Qlik Sense

1 Solution

Accepted Solutions
Master II

script:

island:
Load * Inline [
dim
1
2
];

tab:
load *, AutoNumber(customer&product) as AN,
(customer&product) as key;
load* inline [ customer,product, unitprice
astrida,aa,15
astrida,aa,16
astrida,bb,9
betacap,bb,10
betacap,bb,12
betacap,cc,15
];

straighttable:

dim:     pick(dim,key,'Total')

dim_ background_color:     if(pick(dim,key,'Total')='Total',LightGray(),)

exp1:    sum(unitprice)

exp1_background_color:    if(pick(dim,key,'Total')='Total',LightGray(),)

exp2:    if(pick(dim,key,'Total')='Total',count(AN),count(DISTINCT AN))

exp2_background_color:    if(pick(dim,key,'Total')='Total',LightGray(),)

4 Replies
Master II

script:

tab:
load *, AutoNumber(customer&product) as AN;
load* inline [ customer,product, unitprice
astrida,aa,15
astrida,aa,16
astrida,bb,9
betacap,bb,10
betacap,bb,12
betacap,cc,15
];

straighttable:

dim1: customer

dim2: product

exp1: sum(unitprice)

exp2: count(AN)

Contributor II
Author

Thank you Frank but if i follow you I would have:

 customer product sum(unitprice) count(AN) Totali 77 6 astrida aa 31 2 astrida bb 9 1 betacap bb 22 2 betacap cc 15 1

the new column has two records (astrida,aa,31,2) and (betacap,bb,22,2) but I would have

(astrida,aa,31,1) and (betacap,bb,22,1).

Master II

script:

island:
Load * Inline [
dim
1
2
];

tab:
load *, AutoNumber(customer&product) as AN,
(customer&product) as key;
load* inline [ customer,product, unitprice
astrida,aa,15
astrida,aa,16
astrida,bb,9
betacap,bb,10
betacap,bb,12
betacap,cc,15
];

straighttable:

dim:     pick(dim,key,'Total')

dim_ background_color:     if(pick(dim,key,'Total')='Total',LightGray(),)

exp1:    sum(unitprice)

exp1_background_color:    if(pick(dim,key,'Total')='Total',LightGray(),)

exp2:    if(pick(dim,key,'Total')='Total',count(AN),count(DISTINCT AN))

exp2_background_color:    if(pick(dim,key,'Total')='Total',LightGray(),)

Contributor II
Author

Thank for your help and your kindness.

Arianna

Tags