Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi,
You can try something like this:
If([PO Number] = Below([PO Number]), 0, 1)
You can put this enable conditional in your expression.
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.
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
.
.
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;
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])
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]);
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
You are welcome. Let me know how it goes and remember to drop the temp table at the end.
It worked as wanted, marked as a correct answer.
Thank you again.