Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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

1 Solution

Accepted Solutions
Highlighted
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


View solution in original post

30 Replies
Highlighted
Specialist III
Specialist III

Hi,

You can try something like this:

If([PO Number] = Below([PO Number]), 0, 1)

You can put this enable conditional in your expression.

Highlighted
Contributor II
Contributor II

Thanks for the reply Simon. I am not sure that i understand what i need to do. just so you are aware, there is more than one PO and each PO can have between 1 and 30 rows. I would need all rows excluding for the PO where it meets the above condition.

Highlighted
Specialist III
Specialist III

Or, if you would like to handle it in your load script, then add a line:

Load

       *

Where [Qty on Stock] = 0 And [Job Status] = 'Priced' And Flag = 1;

Load

       *,

        If([PO Number] = Prevoius([PO Number]), 0, 1) As Flag;

SQL SELECT

         .

         .

Highlighted
Contributor II
Contributor II

Simon,

I have inserted the code just before the SQL Select statement as per the code below, but unfortunately it does not return any results. Have I done it wrong?

// Main

SQL  SELECT

"sendac_nl"."name",

"lothed_nl"."ponum",

"lotdet_nl"."uomrecqty",

"lotdet_nl"."soldqty",

"lotdet_nl"."countsize",

"jobbug_nl"."status",

"jobstatus_nl"."descr",

"lotdet_nl"."jobnum",

"poffil_nl"."comment1" as 'PO Notes',

"lotdet_nl"."lotdetid",

"lothed_nl"."type",

"lotdet_nl"."uomrecqty"-"lotdet_nl"."soldqty" as 'OnStock'

FROM   ("Pr2Company1"."dbo"."ordhed_nl" "ordhed_nl"

RIGHT OUTER JOIN ("Pr2Company1"."dbo"."orderstatusdesc_nl" "orderstatusdesc_nl"

RIGHT OUTER JOIN "Pr2Company1"."dbo"."orddet_nl" "orddet_nl" ON "orderstatusdesc_nl"."status"="orddet_nl"."status") ON "ordhed_nl"."ordhedid"="orddet_nl"."ordhedid")

RIGHT OUTER JOIN (("Pr2Company1"."dbo"."jobstatus_nl" "jobstatus_nl"

RIGHT OUTER JOIN "Pr2Company1"."dbo"."jobbug_nl" "jobbug_nl" ON "jobstatus_nl"."status"="jobbug_nl"."status")

RIGHT OUTER JOIN (("Pr2Company1"."dbo"."poffil_nl" "poffil_nl"

RIGHT OUTER JOIN ("Pr2Company1"."dbo"."sendac_nl" "sendac_nl"

RIGHT OUTER JOIN "Pr2Company1"."dbo"."lothed_nl" "lothed_nl" ON "sendac_nl"."supcode"="lothed_nl"."supcode") ON "poffil_nl"."ponum"="lothed_nl"."ponum")

RIGHT OUTER JOIN "Pr2Company1"."dbo"."lotdet_nl" "lotdet_nl" ON "lothed_nl"."lotnum"="lotdet_nl"."lotnum") ON "jobbug_nl"."jobnum"="lotdet_nl"."jobnum") ON "orddet_nl"."lotdetid"="lotdet_nl"."lotdetid"

  WHERE  "lothed_nl"."ponum">10000 AND "lotdet_nl"."uomrecqty"<>0 and "lothed_nl"."type"<>'9' ;

// List of PO's with On Stock = 0

Load

       *

Where [InvQtyOnStock] = 0 And [InvStatus] = 'Priced' And Flag = 1;

Load

       *,

        If([InvPONumber] = Previous([InvPONumber]), 0, 1) As Flag;

SQl  SELECT

"sendac_nl"."name" as 'InvName',

"lothed_nl"."ponum" as 'InvPONumber',

"lotdet_nl"."uomrecqty" as 'InvRecQty',

"lotdet_nl"."soldqty" as 'InvSoldQty',

"lotdet_nl"."countsize" as 'InvCountSize',

"jobbug_nl"."status" as 'InvStatus',

"jobstatus_nl"."descr" as 'InvDesc',

"lotdet_nl"."jobnum" as 'InvJobNum',

"orddet_nl"."status" as 'InvInvoiceStatus',

"orderstatusdesc_nl"."descr" as 'InvInvoiceDesc',

"ordhed_nl"."delcustcode" as 'InvDeliveryCustomer',

"lotdet_nl"."uomrecqty"-"lotdet_nl"."soldqty" as 'InvQtyOnStock',

"jobstatus_nl"."descr" + ' ' + convert(varchar,"lotdet_nl"."uomrecqty"-"lotdet_nl"."soldqty") as 'test'

FROM   ("Pr2Company1"."dbo"."ordhed_nl" "ordhed_nl"

RIGHT OUTER JOIN ("Pr2Company1"."dbo"."orderstatusdesc_nl" "orderstatusdesc_nl"

RIGHT OUTER JOIN "Pr2Company1"."dbo"."orddet_nl" "orddet_nl" ON "orderstatusdesc_nl"."status"="orddet_nl"."status") ON "ordhed_nl"."ordhedid"="orddet_nl"."ordhedid")

RIGHT OUTER JOIN (("Pr2Company1"."dbo"."jobstatus_nl" "jobstatus_nl"

RIGHT OUTER JOIN "Pr2Company1"."dbo"."jobbug_nl" "jobbug_nl" ON "jobstatus_nl"."status"="jobbug_nl"."status")

RIGHT OUTER JOIN (("Pr2Company1"."dbo"."poffil_nl" "poffil_nl"

RIGHT OUTER JOIN ("Pr2Company1"."dbo"."sendac_nl" "sendac_nl"

RIGHT OUTER JOIN "Pr2Company1"."dbo"."lothed_nl" "lothed_nl" ON "sendac_nl"."supcode"="lothed_nl"."supcode") ON "poffil_nl"."ponum"="lothed_nl"."ponum")

RIGHT OUTER JOIN "Pr2Company1"."dbo"."lotdet_nl" "lotdet_nl" ON "lothed_nl"."lotnum"="lotdet_nl"."lotnum") ON "jobbug_nl"."jobnum"="lotdet_nl"."jobnum") ON "orddet_nl"."lotdetid"="lotdet_nl"."lotdetid"

WHERE  "lothed_nl"."ponum">10000 AND "lotdet_nl"."uomrecqty"<>0 and "lothed_nl"."type"<>'9';// and "jobstatus_nl"."descr" + ' ' + convert(varchar,"lotdet_nl"."uomrecqty"-"lotdet_nl"."soldqty") = 'qtys cfm 0.000000';// and "lotdet_nl"."uomrecqty"-"lotdet_nl"."soldqty"=0; // and ("jobbug_nl"."status"=45 or "jobbug_nl"."status"=55);

disconnect;

Highlighted
Not applicable

There are two ways to achieve desired result.

1. Filter in Load Script

2. Filter in expression using set analysis

1. Load Script

Table_Temp:

SQL SELECT;

[Filter PO]:

NOCONCATENATE

LOAD DISTINCT [PO Number] AS [Filter PO]

RESIDENT Table_Temp

WHERE  [Qty on Stock] = 0 And [Job Status] = 'Priced cfm';

Table:

NOCONCATENATE

LOAD * RESIDENT Table_Temp WHERE NOT EXISTS([Filter PO],[PO Number]);

2. Expression using set analysis

Create a dummy expression something like this

COUNT({< [PO Number] -= {$(=CONCAT({<[Qty on Stock]={0},[Job Status]={'Priced cfm'}>} DISTINCT [PO Number],','))}>}  [PO Number])

Highlighted
Contributor II
Contributor II

Thanks Syed,

I guess it is my huge lack of qlikview skills that is causing me not to get this working.

I have got the data going to the temp tables but still not excluding all the records

// List of PO's with On Stock = 0

Table_Temp:

SQl  SELECT

"sendac_nl"."name" as 'InvName',

"lothed_nl"."ponum" as 'InvPONumber',

"lotdet_nl"."uomrecqty" as 'InvRecQty',

"lotdet_nl"."soldqty" as 'InvSoldQty',

"lotdet_nl"."countsize" as 'InvCountSize',

"jobbug_nl"."status" as 'InvStatus',

"jobstatus_nl"."descr" as 'InvDesc',

"lotdet_nl"."jobnum" as 'InvJobNum',

"orddet_nl"."status" as 'InvInvoiceStatus',

"orderstatusdesc_nl"."descr" as 'InvInvoiceDesc',

"ordhed_nl"."delcustcode" as 'InvDeliveryCustomer',

"lotdet_nl"."uomrecqty"-"lotdet_nl"."soldqty" as 'InvQtyOnStock',

"jobstatus_nl"."descr" + ' ' + convert(varchar,"lotdet_nl"."uomrecqty"-"lotdet_nl"."soldqty") as 'test'

FROM   ("Pr2Company1"."dbo"."ordhed_nl" "ordhed_nl"

RIGHT OUTER JOIN ("Pr2Company1"."dbo"."orderstatusdesc_nl" "orderstatusdesc_nl"

RIGHT OUTER JOIN "Pr2Company1"."dbo"."orddet_nl" "orddet_nl" ON "orderstatusdesc_nl"."status"="orddet_nl"."status") ON "ordhed_nl"."ordhedid"="orddet_nl"."ordhedid")

RIGHT OUTER JOIN (("Pr2Company1"."dbo"."jobstatus_nl" "jobstatus_nl"

RIGHT OUTER JOIN "Pr2Company1"."dbo"."jobbug_nl" "jobbug_nl" ON "jobstatus_nl"."status"="jobbug_nl"."status")

RIGHT OUTER JOIN (("Pr2Company1"."dbo"."poffil_nl" "poffil_nl"

RIGHT OUTER JOIN ("Pr2Company1"."dbo"."sendac_nl" "sendac_nl"

RIGHT OUTER JOIN "Pr2Company1"."dbo"."lothed_nl" "lothed_nl" ON "sendac_nl"."supcode"="lothed_nl"."supcode") ON "poffil_nl"."ponum"="lothed_nl"."ponum")

RIGHT OUTER JOIN "Pr2Company1"."dbo"."lotdet_nl" "lotdet_nl" ON "lothed_nl"."lotnum"="lotdet_nl"."lotnum") ON "jobbug_nl"."jobnum"="lotdet_nl"."jobnum") ON "orddet_nl"."lotdetid"="lotdet_nl"."lotdetid"

WHERE  "lothed_nl"."ponum">10000 AND "lotdet_nl"."uomrecqty"<>0 and "lothed_nl"."type"<>'9';// and "jobstatus_nl"."descr" + ' ' + convert(varchar,"lotdet_nl"."uomrecqty"-"lotdet_nl"."soldqty") = 'qtys cfm 0.000000';// and "lotdet_nl"."uomrecqty"-"lotdet_nl"."soldqty"=0; // and ("jobbug_nl"."status"=45 or "jobbug_nl"."status"=55);

[Filter PO]:

NOCONCATENATE

LOAD DISTINCT [InvPONumber] AS [Filter PO]

RESIDENT Table_Temp

WHERE  [InvQtyOnStock] = 0 And [InvStatus] = 'Priced';

Table:

NOCONCATENATE

LOAD * RESIDENT Table_Temp WHERE NOT EXISTS([Filter PO],[InvPONumber]);

Highlighted
Contributor II
Contributor II

I appear to have made a typo and chosen a wrong field for the Filter PO Table.

I will do some more testing but its looking good

Thank You

Highlighted
Not applicable

You are welcome. Let me know how it goes and remember to drop the temp table at the end.

Highlighted
Contributor II
Contributor II

It worked as wanted, marked as a correct answer.

Thank you again.