topic Re: Issue ?? in QlikView

Issue ??

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Issue ??

Gysbert_Wassenaar — Thu, 14 Jul 2016 11:34:07 GMT

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

WHERE

Flag = 'X'

;

LEFT JOIN (T1)

LOAD

Category,

sum(Value) as Value2,

'X' as Flag

RESIDENT

GROUP BY

Re: Issue ??

Thu, 14 Jul 2016 12:14:33 GMT

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.,.,

Re: Issue ??

Gysbert_Wassenaar — Thu, 14 Jul 2016 12:16:08 GMT

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.

Re: Issue ??

Thu, 14 Jul 2016 12:20:57 GMT

but is it possible???,.,

do u have any solution regarding dis,..

Re: Issue ??

Gysbert_Wassenaar — Thu, 14 Jul 2016 12:24:00 GMT

It's probably possible.

Who or what is dis?

Re: Issue ??

Thu, 14 Jul 2016 12:33:21 GMT

do u have any solution regarding this.,.,

Re: Issue ??

Tue, 19 Jul 2016 06:27:44 GMT

Dear team,

Kindly any one have solution regarding this,.,

Please suggest appropriate solution for this issue

Re: Issue ??

Wed, 20 Jul 2016 05:07:33 GMT

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 .

Re: Issue ??

Gysbert_Wassenaar — Wed, 20 Jul 2016 06:16:53 GMT

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

Re: Issue ??

Wed, 20 Jul 2016 07:08:12 GMT

Thank you so much Gysbert Wassenaar its working fine now.,.,

In addition of this one small requirement i need.

If i select part which does not have flag value it should show zero value and if i select part which have flag value 'X'

should show value as addition of part associated with same category

Eg:

If i Select P1 (Flag=' ')should show 0 value and if i select P2(Flag='X') should show 600 value.

what should i do?

Re: Issue ??

Wed, 27 Jul 2016 05:41:23 GMT

hi Gysbert Wassenaar,

have you tried out to attempt this???

Thank you for solution its working but on part selection it is not working as i need .,.

kindly suggest what should i do ???

Re: Issue ??

Gysbert_Wassenaar — Wed, 27 Jul 2016 15:40:31 GMT

have you tried out to attempt this???

No, I can't seem to access your document. For some reason my computer doesn't seem to have a connection to the system where you stored it.

Re: Issue ??

Fri, 29 Jul 2016 09:21:41 GMT

HI,

I had given you dummy data.

rangesum(sum({<Flag={'X'}>}aggr(sum({<Category=P({<Flag={'X'}>} Category)>} total <Category> Value),Category,Part)),sum({<Category=E({<Flag={'X'}>}Category)>}Value)) by this expression i will get my expected output but on Part selection what i want is...

If i select part which does not have flag value it should show zero value and if i select part which have flag value 'X'

should show value as addition of part associated with same category

Eg:

If i Select P1 (Flag=' ')should show 0 value and if i select P2(Flag='X') should show 600 value.

what should i do?

Re: Issue ??

Gysbert_Wassenaar — Fri, 29 Jul 2016 10:26:57 GMT

see attached example