topic Re: aggregating expressions based on different dimension in QlikView

aggregating expressions based on different dimension

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have data like below, where in Date, Status and CID are dimensions and remaining columns are expressions

Date	Status	CID	FAMT	T	LAMT	UN	AN
21/3/2016	Resolved	TA	2	0	0	TA	-
21/3/2016	Resolved	DA	0	0	3	-	DA
21/3/2016	Resolved	RA	0	0	4	-	RA
21/3/2016	Acknowledged	A	0	0	0	A	-
21/3/2016	New	U	5	0	0	U	-
21/3/2016	New	A	26	1	0	A	-

But I need to move 2nd and 3rd row up and show as below

Date	Status	FAMT	T	LAMT	UN	AN
21/03/2016	Resolved	2	0	3	TA	DA
21/03/2016	Resolved	0	0	4	-	RA
21/03/2016	Acknowledged	0	0	0	A	-
21/03/2016	New	5	0	0	U	-
21/03/2016	New	26	1	0	A	-

But if I remove CID, 1st and 2nd row gets combined and I get 7 in 1st row and AN becomes empty.

Re: aggregating expressions based on different dimension

Fri, 11 Nov 2016 07:53:15 GMT

Hi raj,

Upload qvw with small dataset.

Re: aggregating expressions based on different dimension

Fri, 11 Nov 2016 09:06:28 GMT

Hi Lok,

PFA sample excel and qvw.

Name2, Name3 and Name4 should come in the same row as below -

Please note that there is no rule that Name 2, Name 3 and Name 4 will have equal number of entries. It is very much possible that Name2 has U1, U2 and Name3 has A1, A2 and A3 - in this scenario row corresponding to A3 should have blank value in Name2 and Name3.

Date	Name1	Amt1	Status	Name2	Name3	Name4	sum(Amt2)	sum(Amt3)	sum(Amt4)
21/03/2016	P1	5	New	U1	U1	A1	1	0.5	2
21/03/2016	P1	5	New	U2	U2	A2	2	1.5	2

Re: aggregating expressions based on different dimension

varshavig12 — Fri, 11 Nov 2016 09:15:00 GMT

What is the logic behind adding only TA and DA , not RA ?

Re: aggregating expressions based on different dimension

Fri, 11 Nov 2016 10:35:33 GMT

Hi Varsha,

There is no relation between TA, DA and RA.

TA is related to FAMT and DA and RA are related to LAMT.

Thanks

Rajani

Re: aggregating expressions based on different dimension

varshavig12 — Fri, 11 Nov 2016 11:17:07 GMT

Hi Rajani,

Can you please tell me ,

How you got this,

I'm finding it difficult to understand.

Re: aggregating expressions based on different dimension

Fri, 11 Nov 2016 11:45:29 GMT

Hi Varsha,

I have added sample excel and QVW above with different column names to ease my question.

Can you please refer to the excel sheet attached above.

Thanks

Re: aggregating expressions based on different dimension

Fri, 11 Nov 2016 13:00:39 GMT

Hi Varsha,

Apologies was in the middle of something so asked you to check the attached sheets.

Date and Status are dimensions here, AN and UN will hold names of the people to whom amounts are being paid based on different features - for example if feature is ABC, AN and LAMT will have values and if feature is CDE, UN and FAMT will have values.

I have removed feature from the table here because I want to show AN and UN data in the same row, adding feature as dimension will make them appear in different rows. But even after removing feature and converting AN and UN into expressions, I didn't get correct results.

Re: aggregating expressions based on different dimension

varshavig12 — Mon, 14 Nov 2016 04:25:49 GMT

I'm using P.E. so I cannot open your qvw.

Your attached excel sheet has no LAMT, FAMT, AN or something.

I guess now you are giving me the explanation for the earlier sample data.

Assuming the same, do you mean that your feature ABC in explanation = CID in the data ?

Re: aggregating expressions based on different dimension

varshavig12 — Mon, 14 Nov 2016 04:28:15 GMT

stalwar1‌