Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello all,
I am relatively new to Qlikview and have come across an issue that i can not seem to fix.
What I want to be able to do is to exclude rows where ‘Qty on Stock’ = 0 and job status = ‘Priced cfm’. This in itself is simple, but what I also need it to do is to exclude the rest of the records with the same PO number where it meets the criteria.
I am using a SQL Select statement to get the data from SQL databases.
I hope that you understand what i mean.
Thanks,
Alec
Johan,
That is essentially it yes, however i want to keep where it is only QOS=0 and JS=priced cfm and if another QOS or JS appears the whole of the PO is to be discarded.
in the chart >go to expression > and try like this
count({<PONumber=e({<status ={"Priced cfm"},Qty on Stock ={ "0" }>}PONumber)>}PONumber)
Alec,
Please try this after you have loaded your data.
// here you identify which lines follow your criteria
table1:
LOAD *,
if (QOS = 0 and JS = priced, 1, 0) as Flagstatement
RESIDENT 'your loaded table name';
DROP TABLE 'your loaded table name';
// here you identify which PO number have 'other' criteria over your complete dataset
Map_PO:
MAPPING LOAD
PO Number,
1 as FlagPO
RESIDENT table1
WHERE Flagstatement = 0;
// here you map the PO numbers you do not want to keep (code 1)
table2:
LOAD *,
applymap('Map_PO', PO Number) as Remove
Resident table1;
DROP TABLE table1;
// and then finally you remove the undesider Po numbers from your dataset.
table3:
LOAD *
RESIDENT table2
WHERE Remove <> 1;
DROP TABLE table2;
Hope this helps.
Regards,
Johan
Avinash,
I have worked with the expression and it appears to remove some records, but it only removed the single line where it equals the criteria and not all of the lines with the same PO Number.
Thank Johan,
I am just going to step through the code and understand what it does, then hopefully it works
Thanks,
Alec
Hi Alec,
No worries. You should be ok to integrate it in your script. Best to understand may be to do it step by step (table1, then table 2 and finally table 3). I'll be gone soon, but if needed can help you further as of tomorrow morning.
Regards,
Johan
Yes i have worked through and now i understand what it is doing. Just trying to get it to populate table3.
Thanks for the help so far and I will let you know.
Hi Alec,
What happens at the end is that table 3 is concatenated into table 2. This can happen when the content of 2 tables is identical in terms of variables. I see 2 possible reasons why this happens :
1/ Table 3 did not get named correctly, or did not get a name. I don't think this will be the case, but it could.
2/ if table names are correct, then you should add the mention 'noconcatenate' in your script in front of the start of the definition of table 3. That way you force Qlikview to treat both tables separately.
Regards,
Johan
Try this in your calculated dimension instead of PO Number .
IF(‘Qty on Stock’ = 0 and job status = ‘Priced cfm’,PO Number)
Johan,
After testing this it does appear to be working correctly and i can also see the scale-ability of this function.
I will get my users to test to make sure it shows what they expect.