Issue ??

[#1]

Hi Team,

My Dummy data :

Category	Type	Part	Flag	Value
A	L	P1		100
A	M	P2	X	200
A	M	P3		300
B	L	P4		400
B	M	P5	X	500
B	M	P6		600
C	L	P7		700
C	L	P8		800
C	L	P9		900
D	M	P10	X	1000
D	M	P11		1100
D	M	P12		1200

and My Expected Output :

OUTPUT
Category	Part	Value
A	P2	600
B	P5	1500
C	P7	700
C	P8	800
C	P9	900
D	P10	3300

  if Flag ='X' only then value  should be add up and part  colmun should have only that part  which is associated with Flag='X'  not all part and if Flag is not X then it should come as same as  dummy excel

[Gysbert_Wassenaar · Accepted solution]

Here's one solution:

T1:

LOAD * INLINE [

    Category, Type, Part, Flag, Value

    A, L, P1, , 100

    A, M, P2, X, 200

    A, M, P3, , 300

    B, L, P4, , 400

    B, M, P5, X, 500

    B, M, P6, , 600

    C, L, P7, , 700

    C, L, P8, , 800

    C, L, P9, , 900

    D, M, P10, X, 1000

    D, M, P11, , 1100

    D, M, P12, , 1200

];

T2:

LOAD DISTINCT

  Category as Cat_Check

RESIDENT

  T1

WHERE

  Flag = 'X'

  ;

LEFT JOIN (T1)

LOAD

  Category,

  sum(Value) as Value2,

  'X' as Flag

RESIDENT

  T1

GROUP BY

  Category

  ;

T3:

LOAD Category, Type, Part, Flag, Value2 as Value RESIDENT T1 WHERE Flag = 'X';

LOAD Category, Type, Part, Flag, Value as Value RESIDENT T1 WHERE NOT Exists(Cat_Check,Category);

DROP TABLES T1, T2;

[#3]

Thanq u GD Wassenaar,

Can we do it same thing in frontend?? Is it possible ???,.,.,

My application is to heavy to do this task at backend level.,.,

[Gysbert_Wassenaar]

My application is to heavy to do this task at backend level.,.,

Then it will certainly be too heavy to do it in the front end.

[#5]

but is it possible???,.,

do u have any solution regarding dis,..

[Gysbert_Wassenaar]

It's probably possible.

Who or what is dis?

[#7]

do u have any solution regarding this.,.,

[#8]

Dear team,

Kindly any one have solution regarding this,.,

Please suggest appropriate solution for this issue

[#9]

Hi Gysbert,

Thank u for solution.Its working fine at backend level but i want same thing at front end because don't want to disturb data model of application.

Kindly suggest any other solution,its urgent .

[Gysbert_Wassenaar]

rangesum(sum({<Flag={'X'}>}aggr(sum({<Category=P({<Flag={'X'}>} Category)>} total <Category> Value),Category,Part)),sum({<Category=E({<Flag={'X'}>}Category)>}Value))