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

[Anil_Babu_Samineni]

I didn't see any Dept field in your table? Would you let us exactly the logic behind

[Anonymous]

I make a mistake.

It should be Type.

Sorry about that.

Many Thanks.

[Anil_Babu_Samineni]

Oh okay, How A & extra cost clubbed. Are you sure about Output? For me, Expectation is not reachable. Can you explain little more with data and formula to achieve?

[Anonymous]

Hi,

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

Best Regards,

Louis

[Anil_Babu_Samineni]

Yes, It is and i got it as below when i use below script

Sample:

LOAD ReceiptCode, LineNo, If(Type='extra cost', 'A', Type) as Type, Value

FROM [https://community.qlik.com/message/1302514]

(html, codepage is 1252, embedded labels, table is @1) Where Value > 0;

Final:

LOAD ReceiptCode, Type, Sum(Value) as Value Resident Sample Group By ReceiptCode,Type;

DROP Table Sample;

Output:

[Anonymous]

It is not only happening in A.

I have edited the table and it should be included all possible cases.

Many Thanks

[Anil_Babu_Samineni]

I am not sure, What you are talking about? Does my reply is not helpful for you? Because, If you use first part it will return 3rd table and entire script generates the second table. Aren't you?

[Anonymous]

Your script will turn all "extra cost" to "A".

However, i would like to turn "extra cost" to the type before (other than "extra cost")

[Anil_Babu_Samineni]

I hope i understand the issue now?

1) Create Flag like If(Type='extra cost', 1, Type)

2) Use Pick(Match(Type, 1), 'extra cost', Type) with Inner Join

3) Then use NoConcatenate for that inner table

You may achieve that as expected