Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am new to Qlik Sense and need help on below issue urgently.
I have a Table as follows:
I have field names Product Name in which there are different Products :
Product Name
S1
S2
S3
S4
S5
S6
Custid | Product Name |
---|---|
1 | S1 |
1 | S2 |
2 | S3 |
2 | S1 |
and i am looking for output like:-
Custid | Products bought | Products not bought |
---|---|---|
1 | S1,S2 | S3,S4,S5,S6 |
2 | S3,S1 | S2,S4,S5,S6 |
Actually in the Output of Products not bought I want all product name which are not bought by the Customer and they must be comma seperated.
For Products Brought
Concat(Distinct [Product Name],',')
Take a look at the concat() function.
Thnx for reply.. but it want solution for products which are not bought..
hi,
can you please help me for syntax..
hi pradya,
Try this:
Table:
LOAD *,
1 as Flag;
LOAD * INLINE [
CustomID, Items
1, S1
1, S2
2, S3
2, S1
3, S2
3, S3
];
Temp:
LOAD DISTINCT Items as AllItems
Resident Table;
Left Join(Temp)
LOAD Distinct CustomID
Resident Table;
Join (Table)
LOAD CustomID,
AllItems as Items
Resident Temp;
FinalTable:
LOAD CustomID,
Concat(If(Flag = 1, Items), ', ') as [Items Bought],
Concat(If(Flag <> 1, Items), ', ') as [Items Not Bought]
Resident Table
Group By CustomID;
DROP Tables Temp, Table;