aggregating expressions based on different dimension

[#1]

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.

[#2]

Hi raj,

Upload qvw with small dataset.

[#3]

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

[varshavig12]

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

[#5]

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

[varshavig12]

Hi Rajani,

Can you please tell me ,

How you got this,

I'm finding it difficult to understand.

[#7]

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

[#8]

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.

[varshavig12]

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 ?

[varshavig12]

stalwar1‌