Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
alecpickles
Contributor II
Contributor II

Filtering of Records

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

30 Replies
alecpickles
Contributor II
Contributor II
Author

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.

avinashelite

in the chart >go to expression > and try like this

count({<PONumber=e({<status ={"Priced cfm"},Qty on Stock ={ "0" }>}PONumber)>}PONumber)

puttemans
Specialist
Specialist

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


alecpickles
Contributor II
Contributor II
Author

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.

alecpickles
Contributor II
Contributor II
Author

Thank Johan,

I am just going to step through the code and understand what it does, then hopefully it works

Thanks,

Alec

puttemans
Specialist
Specialist

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

alecpickles
Contributor II
Contributor II
Author

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.

puttemans
Specialist
Specialist

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

sunilkumarqv
Specialist II
Specialist II

Try this in your calculated dimension instead of PO Number .

IF(‘Qty on Stock’ = 0 and job status = ‘Priced cfm’,PO Number)

alecpickles
Contributor II
Contributor II
Author

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.