Need to create a new ID for a group of products?

[#1]

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!

[rubenmarin]

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)

[sunny_talwar]

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

[#4]

Ruben, when I try this code it errors because the "bundle" In the the Distinct(bundle) statement doesn't exist.  Can you advise?   Thanks!

[rubenmarin]

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”'

[Ralf-Narfeldt]

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?