Separate transaction rows for bundled products

[ThomasS]

Hi,

I have a dataset where a bundled product (consists of more than one sub component) has to be split up in different rows of data because they are subject to different VAT-rates. I have found a similar case that handles this issue with a Python script, but I was hoping that this could also be achieved in the load script of Qlik Sense? 

https://stackoverflow.com/questions/50251408/separate-transaction-rows-of-bundled-products-into-mult... 

Thanks in advance for the help! 

[marcus_sommer]

Of course there are script-solutions in Qlik possible. One might be:

m: mapping load ParentSKU, concat(ComponentSKU & '|' & Quantity, chr(1)) from Bundles;

t:
load *, Qty * Qty2 as Qty3; // any further transformations
load *, subfield(Product2, '|', 1) as Product3, subfield(Product2, '|', 2) as Qty2;
load *, subfield(Product, chr(1)) as Product2;
load Date, Product, applymap('m', Product, Product) as Product1, Qty from Transactions;

- Marcus

[ThomasS]

Hi Marcus, 

Thanks so much for your reply! 

I am getting a load error. I think it has something to do with the concatenation in the mapping load?

-->If i change the 'concat(ComponentSKU & '|' & Quantity, chr(1))' to 'ComponentSKU&'|'&Quantity&chr(1)' it does'nt give me any errors (but that doesn't give me the proper table). 

You wouldn't have any further suggestions? I've added the script form Qlik Sense . 

Much appreciated if you could help me out! 

Kind regards,

Thomas

[TRANSACTIONS]:
LOAD * INLINE [
Date, Product, Qty
1 Jan 2017, A, 10
2 Jan 2017, Bundle X, 5
3 Jan 2017, B, 10
4 Jan 2017, Bundle Y, 5];

[BUNDLES]:
LOAD * INLINE [
ParentSKU, ComponentSKU, Quantity
Bundle X, A, 3
Bundle X, B, 5
Bundle X, C, 10
Bundle Y, P, 5
Bundle Y, Q, 7
Bundle Y, R, 12
Bundle Y, S, 3];

[m]:
MAPPING LOAD ParentSKU, ComponentSKU&'|'&Quantity&chr(1) as m
RESIDENT BUNDLES;

[t]:
load *, Qty * Qty2 as Qty3; // any further transformations
load *, subfield(Product2, '|', 1) as Product3, subfield(Product2, '|', 2) as Qty2;
load *, subfield(Product, chr(1)) as Product2;
load Date, Product, applymap('m', Product, Product) as Product1, Qty
Resident Transactions;

[MayilVahanan]

Hi @ThomasS 

If you are using Concat() function, you need to use Group by

Try like below

[m]:
MAPPING LOAD ParentSKU, Concat(ComponentSKU&'|'&Quantity,chr(1)) as m
RESIDENT BUNDLES
Group by ParentSKU;

And also, Qlik is case sensitive, table name "TRANSACTIONS" is not Transactions

[marcus_sommer · Accepted solution]

What for an error? Ah, I see I missed the group by within the aggregation load - change it to:

m: mapping load ParentSKU, concat(ComponentSKU & '|' & Quantity, chr(1))
from Bundles group by ParentSKU;

- Marcus

[ThomasS]

Thanks Marcus_sommer & MayilVahanan, 

The Group BY clause was indeed missing. I got through the script without any errors this time. I had to make one more adjustment in the script: 

load *, subfield(Product1, chr(1)) as Product2 instead of load *, subfield(Product, chr(1)) as Product2

Thanks you very much for the patience! 

Kind regards,
Thomas

[TRANSACTIONS]:
LOAD * INLINE [
Date, Product, Qty
1 Jan 2017, A, 10
2 Jan 2017, Bundle X, 5
3 Jan 2017, B, 10
4 Jan 2017, Bundle Y, 5];

[BUNDLES]:
LOAD * INLINE [
ParentSKU, ComponentSKU, Quantity
Bundle X, A, 3
Bundle X, B, 5
Bundle X, C, 10
Bundle Y, P, 5
Bundle Y, Q, 7
Bundle Y, R, 12
Bundle Y, S, 3];

[m]:
MAPPING LOAD ParentSKU, Concat(ComponentSKU&'|'&Quantity,chr(1)) as m
RESIDENT BUNDLES
Group by ParentSKU;

[t]:
load *, Qty * Qty2 as Qty3; // any further transformations
load *, subfield(Product2, '|', 1) as Product3, subfield(Product2, '|', 2) as Qty2;
load *, subfield(Product1, chr(1)) as Product2;
load Date, Product, applymap('m', Product, Product) as Product1, Qty
Resident TRANSACTIONS;