Transformation of Data crosstable

[arsenal1983]

Hello,

I need to transform my data durig load process by using some kind of crosstable. 

I have data like:

DOC_KEY	INVOICE_NO	TEXT	VOUCHER_FACE_VALUE	ACOUNT	AMOUNT
1200000164-XXXX-2019	1250036085	*eVoucher 5 USD gross FV:2019.01:H	5	871080	-4477
1200000164-XXXX-2019	1250036085	*eVoucher 5 USD gross FV:2019.01:S	5	871081	282,05
1200000164-XXXX-2019	1250036085	*eVoucher 5 USD gross FV:2019.01:S	5	510700	3,95
1200000164-XXXX-2019	1250036085	*eVoucher 10 USD gross FV:2019.01:H	10	871080	-20325
1200000164-XXXX-2019	1250036085	*eVoucher 10 USD gross FV:2019.01:H	10	510700	-5,47
1200000164-XXXX-2019	1250036085	*eVoucher 10 USD gross FV:2019.01:S	10	871081	1280,47
1200000164-XXXX-2019	1250036085	*eVoucher 30 USD gross FV:2019.01:H	30	871080	-41463
1200000164-XXXX-2019	1250036085	*eVoucher 30 USD gross FV:2019.01:S	30	871081	2612,16
1200000164-XXXX-2019	1250036085	*eVoucher 30 USD gross FV:2019.01:S	30	510700	5,84
1200000164-XXXX-2019	1250036085	*eVoucher 50 USD gross FV:2019.01:H	50	871080	-20325
1200000164-XXXX-2019	1250036085	*eVoucher 50 USD gross FV:2019.01:H	50	510700	-0,47
1200000164-XXXX-2019	1250036085	*eVoucher 50 USD gross FV:2019.01:S	50	871081	1280,47
1200000164-XXXX-2019	1250036085	*eVoucher 60 USD gross FV:2019.01:H	60	871080	-2439
1200000164-XXXX-2019	1250036085	*eVoucher 60 USD gross FV:2019.01:H	60	510700	-0,15
1200000164-XXXX-2019	1250036085	*eVoucher 60 USD gross FV:2019.01:S	60	871081	153,65
1200000164-XXXX-2019	1250036085	*eVoucher 100 USD gross FV:2019.01	100	110125	107287,37
1200000164-XXXX-2019	1250036085	*eVoucher 100 USD gross FV:2019.01:H	100	871080	-4065
1200000164-XXXX-2019	1250036085	*eVoucher 100 USD gross FV:2019.01:H	100	510700	-0,09
1200000164-XXXX-2019	1250036085	*eVoucher 100 USD gross FV:2019.01:S	100	871081	256,09
1200000165-XXXX-2019	1250036086	*eVoucher 5 USD gross FV:2019.01:H	5	871080	-4070
1200000165-XXXX-2019	1250036086	*eVoucher 5 USD gross FV:2019.01:H	5	510700	-4,2
1200000165-XXXX-2019	1250036086	*eVoucher 5 USD gross FV:2019.01:S	5	871081	244,2

I expect result:

DOC_KEYINVOICE_NOVOUCHER_FACE_VALUE1101201101255107008710808710811200000164-XXXX-201912500360851200000164-XXXX-201912500360851200000164-XXXX-201912500360851200000164-XXXX-201912500360851200000164-XXXX-201912500360851200000164-XXXX-201912500360851200000165-XXXX-201912500360861200000165-XXXX-201912500360861200000165-XXXX-201912500360861200000165-XXXX-201912500360861200000165-XXXX-201912500360861200000205-XXXX-201912500360871200000205-XXXX-20191250036087

5			3,95	-4477	282,05
10			-5,47	-20325	1280,47
30			5,84	-41463	2612,16
50			-0,47	-20325	1280,47
60			-0,15	-2439	153,65
100		107287,37	-0,09	-4065	256,09
5			-4,2	-4070	244,2
10			3,3	-12195	731,7
30			-3,4	-24390	1463,4
50			0,3	-12195	731,7
60	63929,87		0,16	-2439	146,34
5			-8,4	-8140	488,4
10			17,6	-65040	3902,4

[sunny_talwar]

Can you delimit your output data so that it can become a little more readable.

[arsenal1983]

Hi sunny, I've attached the data set - input and output in xlsx have you seen it? Output is in piv sheet, the sample below.

DOC_KEY;INVOICE_NO;VOUCHER_FACE_VALUE;110120;110125;510700;871080;871081
1200000164-XXXX-2019;1250036085;5;;;3,95;-4477;282,05
1200000164-XXXX-2019;1250036085;10;;;-5,47;-20325;1280,47

[sunny_talwar · Accepted solution]

Something like this

Table:
LOAD DOC_KEY,
INVOICE_NO,
TEXT,
VOUCHER_FACE_VALUE,
ACOUNT,
AMOUNT
FROM
[..\..\Downloads\VOUCH (1).xlsx]
(ooxml, embedded labels, table is Sheet1);

FinalTable:
LOAD DISTINCT DOC_KEY,
INVOICE_NO,
VOUCHER_FACE_VALUE
Resident Table;

FOR i = 1 to FieldValueCount('ACOUNT')

LET vACOUNT = FieldValue('ACOUNT', $(i));

Left Join (FinalTable)
LOAD DISTINCT DOC_KEY,
INVOICE_NO,
VOUCHER_FACE_VALUE,
AMOUNT as [$(vACOUNT)]
Resident Table
Where ACOUNT = '$(vACOUNT)';

NEXT

DROP Table Table;