Filtering of Records

[alecpickles]

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

[#2]

Your welcome..

[awhitfield]

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

Andy

[alecpickles]

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.

[#5]

Could you give me an example?

[alecpickles]

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

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] = '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.

[alecpickles]

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.

[alecpickles]

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

[avinashelite]

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

[alecpickles]

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

[puttemans]

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