Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
From the data below I have to find products which were sold together in different ID's.
For example, the combination of Product1 and Product3 were sold 3 times where Product 1 sold 3 pieces as well as Product3 has been sold 7 pieces.
So I need result like this
ProductsTogether | Sold times together | Product 1st | Sold pieces | Product 2nd | Sold pieces |
Product1&Product3 | 3 times | Product1 | 3 pieces | Product3 | 7 pieces |
Product1&Product2 | 1 time | Product1 | 1 piece | Product2 | 5 pieces |
-- etc --
Think such task should be done before. Please share your experience with me.
Thank you in advance!
Table:
LOAD * Inline
[ID, Product, Sold quantity
001, Product1, 1
001, Product2, 5
001, Product3, 1
002, Product1, 1
002, Product3, 2
003, Product1, 1
003, Product3, 4
003, Product5, 2
]
Hello, Max!
For solving such tasks the best way is using a matrix, especially if you want to cross all with all.
The script will be:
temp:
LOAD * Inline
[ID, Product, Sold quantity
001, Product1, 1
001, Product2, 5
001, Product3, 1
002, Product1, 1
002, Product3, 2
003, Product1, 1
003, Product3, 4
003, Product5, 2
];
Join(temp)
LOAD ID, Product as Product2,[Sold quantity] as [Sold quantity2] Resident temp;
After that you can create a matrix:
And of course you can change an expression with output string by your taste.
P.S.: If you find my solution helpful or correct, please, close the thread (Qlik Community Tip: Marking Replies as Correct or Helpful). !
Hello, Max!
For solving such tasks the best way is using a matrix, especially if you want to cross all with all.
The script will be:
temp:
LOAD * Inline
[ID, Product, Sold quantity
001, Product1, 1
001, Product2, 5
001, Product3, 1
002, Product1, 1
002, Product3, 2
003, Product1, 1
003, Product3, 4
003, Product5, 2
];
Join(temp)
LOAD ID, Product as Product2,[Sold quantity] as [Sold quantity2] Resident temp;
After that you can create a matrix:
And of course you can change an expression with output string by your taste.
P.S.: If you find my solution helpful or correct, please, close the thread (Qlik Community Tip: Marking Replies as Correct or Helpful). !