QlikView Logic Implementation

[ujjwalraja]

Hi Qlik Team,

I have data as following

key	receipt
1	0
1	122
1	256
2	0
3	0
3	256
4	586

The requirement is to fetch only those key for which no receipt has been generated. Here the output should be key 2

Please suggest logic , I have to do it in back-end.

Thanks

[vamsee]

LOAD

          *

From Table

Where

Num(receipt) > 0;

[ujjwalraja]

This will give 1,3 and 4

[Anil_Babu_Samineni]

You want this in Script / UI?

[ujjwalraja]

script

[vamsee]

Maybe try

Source:

LOAD * Inline [

    key, receipt

    1, 0

    1, 122

    1, 256

    2, 0

    3, 0

    3, 256

    4, 586

];

Min:

LOAD key as Key_Check

Where minvalue=0;

LOAD

key,

min(receipt) as minvalue

Resident Source

Group by key;

Final:

NoConcatenate

LOAD

*

Resident Source

Where Not Exists (Key_Check, key);

Drop Tables Min, Source;

[raman_rastogi · Accepted solution]

May be like this

Input1:

Load * Inline [

Key   , reciept

1, 0

2, 0

3, 0

1, 122

1, 256

3, 256

4, 586

];

NoConcatenate

I2:

LOAD *, Key as Keycheck RESIDENT Input1

WHERE reciept <>0;

CONCATENATE

LOAD *, Key as Keycheck RESIDENT Input1

WHERE reciept =0  and not exists(Keycheck,Key);

NoConcatenate

Load

Key ,reciept Resident I2 where reciept=0;

Drop table Input1;

Drop table I2;

Regards

Raman

[snehamahaveer]

Hello Ujjwal,

I would suggest loading like

Load

Key,

sum(Receipt) as Total

from table

Group by Key;

and then do

Table 2:

Load

Key

Resident table

where Total = 0;

Hope this helps.

Regards

Sneha