topic Re: Separate transaction rows for bundled products in Connectivity & Data Prep

Separate transaction rows for bundled products

ThomasS — Sun, 18 Apr 2021 10:04:27 GMT

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-multiple-rows-of-individual-p

Thanks in advance for the help!

Re: Separate transaction rows for bundled products

marcus_sommer — Mon, 19 Apr 2021 08:55:26 GMT

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

Re: Separate transaction rows for bundled products

ThomasS — Wed, 21 Apr 2021 10:19:41 GMT

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;

Re: Separate transaction rows for bundled products

MayilVahanan — Wed, 21 Apr 2021 10:32:40 GMT

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

Re: Separate transaction rows for bundled products

marcus_sommer — Wed, 21 Apr 2021 10:34:36 GMT

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

Re: Separate transaction rows for bundled products

ThomasS — Wed, 21 Apr 2021 11:45:51 GMT

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;