Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
LOAD
*
From Table
Where
Num(receipt) > 0;
This will give 1,3 and 4
You want this in Script / UI?
script
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;
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
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