Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
My data has a Transaction_Id (Trx_Id) that can have 1 to 3 Product Types (A,B,C) called a “bundle”. Currently in my data there is no way to “group” or identify these Products as a bundle. For example, if I want to filter-out any transaction that has product type A, I can exclude that product, but it doesn’t exclude the entire transaction…it leaves the transaction in the table because B and C are still there. Ideally, I want to filter-out any “bundle” that contains product type A. Does that make sense? Is there some way in the script to create some sort of Bundle_ID which will allow me to do this?
Thanks!
Hi Dan, not tested but maybe you can create a bundle_ID field using all product types related to a transaction, something like:
Data:
LOAD.....
Bundles:
NoConcatenate LOAD Trx_Id,
Concat(DISTINT bundle) as Bundle_ID
Resident Data Group By Trx_Id;
Left Join (Data) LOAD * resident Bundles;
DROP table Bundles;
Also with your current script you can exclude Trx_ID with bundle=A using the E() funtion in set analysis, ie:
Sum({<Trx_Id = E({1<Bundle={‘A’}>} Sales)>} Sales)
If I understand you correctly, Why don't you try adding a inline table like this which creates a Bundle ID for each product time
LOAD * INLINE [
Bundle ID, Product Type
1, A
1, B
1, C
...
];
Next time you want to select or exclude ABC together you can do it based on Bundle ID
I might have mis-understood your requirement, so please don't be angry if I did 😉
Best,
S
Ruben, when I try this code it errors because the "bundle" In the the Distinct(bundle) statement doesn't exist. Can you advise? Thanks!
Hi Dan, I don't know the real name of the field, i write 'bundle' meaning the field where you are storing the 'Product Types (A,B,C) called a “bundle”'
I think it would help if you gave some more details about your data, especially about the "bundle". Is it a separate field, or is it a part of the Transaction_Id or some other field?