POS Data with grouping in Qlikview

[Anonymous]

Hi all,

I would like to group the value of the Table 1 base on Type to Table 2.

or

is it possible to modify from table 1 to table 3?

Anyway to do it in loading script?

Table 1

ReceiptCode	LineNo	Type	Value
1001	1	A	10
1001	2	extra cost	1
1001	3	extra cost	3
1001	4	B	12
1002	1	A	10
1002	2	B	12
1003	1	C	20
1003	2	extra cost	0
1004	1	B	12
1004	2	extra cost	2
1004	3	C	20
1004	4	extra cost	2

Table 2

ReceiptCode	LineNo	Type	Value
1001	1	A	14
1001	4	B	12
1002	1	A	10
1002	2	B	12
1003	1	C	20
1004	1	B	12
1004	3	C	22

Table 3

ReceiptCode	LineNo	Type	Value
1001	1	A	10
1001	2	A	1
1001	3	A	3
1001	4	B	12
1002	1	A	10
1002	2	B	12
1003	1	C	20
1003	2	C	0
1004	1	B	12
1004	2	B	2
1004	3	C	20
1004	4	C	2

Best Regards,

Louis

[antoniotiman · Accepted solution]

Hi Louis

Temp:
LOAD ReceiptCode,
LineNo,
If(Type='extra cost',Peek(Type),Type) as Type,
Value
FROM
"https://community.qlik.com/message/1302513"
(html, codepage is 1252, embedded labels, table is @1);
NoConcatenate
LOAD ReceiptCode,FirstSortedValue(Type,LineNo) as Type,
Sum(Value) as Value,Min(LineNo) as LineNo
Resident Temp
Group By ReceiptCode,Type;
Drop Table Temp;

Regards,

Antonio