Help needed for table visualization

[noviceneil]

Hello Experts, 

                           I need some help on the following,

I have a Input table A which can have more than 500 rows. In the Values column, for same row level 3 kind of values provided. Like ,

ABC_10_10	01/01/2020	10	10	15000
ABC_10_30	01/01/2020	10	30	0.72
ABC_10_40	01/01/2020	10	40	10800

Here for same Row ABC_10, the first row is the current value, 2nd row is the applicable percentage and 3rd row is the  derived current value (row1*row2). Following same logic , the final expected basic output is given below

INPUT TABLE A:

Key	Date	ROW	Column	Values
ABC_10_10	01/01/2020	10	10	15000
ABC_10_30	01/01/2020	10	30	0.72
ABC_10_40	01/01/2020	10	40	10800
ABC_20_10	01/01/2020	20	10	45000
ABC_20_30	01/01/2020	20	30	0.93
ABC_20_40	01/01/2020	20	40	41850
ABC_30_10	01/01/2020	30	10	60000
ABC_30_30	01/01/2020	30	30	0.87
ABC_30_40	01/01/2020	30	40	52200
ABC_10_10	01/02/2020	10	10	15500
ABC_10_30	01/02/2020	10	30	0.72
ABC_10_40	01/02/2020	10	40	11160
ABC_20_10	01/02/2020	20	10	45200
ABC_20_30	01/02/2020	20	30	0.93
ABC_20_40	01/02/2020	20	40	42036
ABC_30_10	01/02/2020	30	10	61000
ABC_30_30	01/02/2020	30	30	0.87
ABC_30_40	01/02/2020	30	40	53070

OUTPUT Needed Basic visualization :

Row	App. Perc.	CurrentValue	Previous Value	Difference
ABC_10	0.72	11160	10800	360
ABC_20	0.93	42036	41850	186
ABC_30	0.87	53070	52200	870

In theory, I want to show the percentages ( values with column id 30) in a  column "App. Perc.",  all values with date 01/02/2020 and column id 40 in column "Current Value" and all values with date 01/01/2020 and column id 40 in column "Previous Value" and Difference getting calculated by Current Value - Previous Value.

Any lead will be very much helpful.

[Digvijay_Singh]

May be something like this in attached qvf

[JuanGerardo]

Hi @noviceneil, you can use the group by clause to get all your data aligned by row and date:

InputTable:
Load
Left(Key, 6) AS Row,
Date,
If(Column = 10, Values) AS CurrentValue,
If(Column = 30, Values) AS AppPerc,
If(Column = 40, Values) AS DerivedValue
Inline [
Key,Date,ROW,Column,Values
ABC_10_10, 01/01/2020, 10, 10, 15000
ABC_10_30, 01/01/2020, 10, 30, 0.72
ABC_10_40, 01/01/2020, 10, 40, 10800
ABC_20_10, 01/01/2020, 20, 10, 45000
ABC_20_30, 01/01/2020, 20, 30, 0.93
ABC_20_40, 01/01/2020, 20, 40, 41850
ABC_30_10, 01/01/2020, 30, 10, 60000
ABC_30_30, 01/01/2020, 30, 30, 0.87
ABC_30_40, 01/01/2020, 30, 40, 52200
ABC_10_10, 01/02/2020, 10, 10, 15500
ABC_10_30, 01/02/2020, 10, 30, 0.72
ABC_10_40, 01/02/2020, 10, 40, 11160
ABC_20_10, 01/02/2020, 20, 10, 45200
ABC_20_30, 01/02/2020, 20, 30, 0.93
ABC_20_40, 01/02/2020, 20, 40, 42036
ABC_30_10, 01/02/2020, 30, 10, 61000
ABC_30_30, 01/02/2020, 30, 30, 0.87
ABC_30_40, 01/02/2020, 30, 40, 53070
];

OutputTable:
NoConcatenate
Load
Row,
Date,
Avg(AppPerc) AS AppPerc,
Sum(CurrentValue) AS CurrentValue,
Sum(DerivedValue) AS DerivedValue
Resident InputTable
Group By Row, Date;

Drop Table InputTable;

You will get a table like this:

So now, operate with this data can make very easy the expected calculations, for example:

Difference = Sum({<Date={'$(=Max(Date))'}>} CurrentValue) - Sum({<Date={'$(=Max(Date) - 1)'}>} CurrentValue)

JG