topic Re: Filtering of Records in QlikView

Filtering of Records

alecpickles — Thu, 23 Jul 2015 14:40:32 GMT

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

Re: Filtering of Records

sinanozdemir — Thu, 23 Jul 2015 14:54:50 GMT

Hi,

You can try something like this:

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

You can put this enable conditional in your expression.

Re: Filtering of Records

alecpickles — Thu, 23 Jul 2015 14:59:57 GMT

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.

Re: Filtering of Records

sinanozdemir — Thu, 23 Jul 2015 15:06:05 GMT

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

Re: Filtering of Records

alecpickles — Thu, 23 Jul 2015 15:17:26 GMT

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")

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;

Re: Filtering of Records

Thu, 23 Jul 2015 15:25:39 GMT

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])

Re: Filtering of Records

alecpickles — Thu, 23 Jul 2015 15:50:39 GMT

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")

[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]);

Re: Filtering of Records

alecpickles — Thu, 23 Jul 2015 16:11:19 GMT

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

Re: Filtering of Records

Thu, 23 Jul 2015 16:46:08 GMT

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

Re: Filtering of Records

alecpickles — Fri, 24 Jul 2015 06:21:34 GMT

It worked as wanted, marked as a correct answer.

Thank you again.

Re: Filtering of Records

Fri, 24 Jul 2015 12:25:59 GMT

Your welcome..

Re: Filtering of Records

awhitfield — Fri, 24 Jul 2015 14:23:00 GMT

PS - In future, if in doubt post you question in 'New to Qlikview', most people watch that one

Andy

Re: Filtering of Records

alecpickles — Fri, 24 Jul 2015 14:24:40 GMT

Just to add to the question, I thought it did exactly what i was after, however, it appears that it is excluding PO's where it meets the exact criteria.

However as there are many jobs per PO and if one of those PO's has a job row that does not meet the criteria, this row is excluded but the rest of the record is still shown.

Re: Filtering of Records

Fri, 24 Jul 2015 15:58:39 GMT

Could you give me an example?

Re: Filtering of Records

alecpickles — Mon, 27 Jul 2015 06:24:12 GMT

Hi Syed,

In the image attached you will see that there are both Completed and Price cfm jobs showing, these are status 55 and status 45. When running the following code the filter po field should not show 11043 as not all the lines show Completed, however it does show 11403 as a selectable PO.

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")

[Filter PO]:

NOCONCATENATE

LOAD DISTINCT [InvPONumber] AS [Filter PO]

RESIDENT Table_Temp

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

Table:

NOCONCATENATE

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

It does work when all of the lines are the same status or no status.

Re: Filtering of Records

alecpickles — Mon, 27 Jul 2015 11:32:09 GMT

Here are two examples with data showing. The first one shows the result after using the code above and the second shows the raw data. AS you can see it has simply removed the rows which do not comply with the query, rather than removing the whole of the PO.

Thank you for your massive help so far, just really hope what i am trying to do is possible.

Re: Filtering of Records

alecpickles — Tue, 28 Jul 2015 12:48:14 GMT

Does anyone know if what i am trying to achieve is possible. thank you

Re: Filtering of Records

avinashelite — Tue, 28 Jul 2015 12:57:19 GMT

can you please answer few of the Question below:

1.you want to eliminate the duplicate PO data?

2.You want to eliminate the data of PO with conditions where ‘Qty on Stock’ = 0 and job status = ‘Priced cfm’??

3.you want to do it in QV or SQL?

if you want to do in SQL then your can do a group by on the ‘Qty on Stock and status

Re: Filtering of Records

alecpickles — Tue, 28 Jul 2015 13:03:15 GMT

Thanks for the reply,

I want to exclude all records with the same PO number in the event that any one of the records not equal my criteria.

In the example above, there are a number of entries for PO 11412 and if one of those entries has a job status <> Price cfm then i do not want it to return any record for that PO number.

I am not sure what would be better, QV or SQL.

Thanks,
Alec

Re: Filtering of Records

puttemans — Tue, 28 Jul 2015 13:08:58 GMT

Hi Alec,

So any PO that contains on one of his lines somewhere quantity on stock = 0 and job status = priced cfm needs to go?

This is possible, but I'd work with an intermediate mapping table to identify the concerned PO-numbers first and then remove them with a where clause.

Regards,

Johan