Solved: Filtering of Records - Qlik Community

alecpickles · ‎2015-07-23

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

puttemans · ‎2015-07-28

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

sinanozdemir · ‎2015-07-23

Hi,

You can try something like this:

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

You can put this enable conditional in your expression.

alecpickles · ‎2015-07-23

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.

sinanozdemir · ‎2015-07-23

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

.

alecpickles · ‎2015-07-23

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'