Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all, this seems like such a simple issue but i just cannot get it to work.
I have two tables as per the attached images, One has a "complete" list of Sales Orders and Sales Order Lines. The other has only sales order / Sales Order lines that have stock reservations against them.
Ultimately I need to sum up all the reservations by product, hence why i can't have any nulls in my table.
At the moment, where there are no data Matches, I am getting a NULL. I want to convert this into a 0.
I am using IF(isNull("qty_reserve"),'0',"qty_reserve") as PBSSOLineQTYReserved; but have tried using Len() and various other combinations and functions but it just won't work...
Is anyone able to shed some light please?
here is my script:
PBSSOrdersActive:
Load
"s_order" as PBSSOrder,
"ent_code_ware" as PBSSOrderWHEnt;
SQL SELECT
s_order,
ent_code_ware
FROM pub.sorder where sorder.so_status = 2 AND sorder.ent_code_ware IN ('hc','HC');
Left JOIN (PBSSOrdersActive)
PBSSOrdLine:
Load
"s_order" as PBSSOrder,
SKU as PBSSKU,
lineno as PBSSOLineLineNo,
"qty_onorder" as PBSSOLineQTYonOrder,
"qty_picking" as PBSSOLineQTYinPicking;
SQL SELECT
s_order,
SKU,
lineno,
qty_onorder,
qty_picking
FROM pub.sordline;
Left Join (PBSSOrdersActive)
PBSReserve:
Load
"doc_num" as PBSSOrder,
"lineno_doc" as PBSSOLineLineNo,
IF(isNull("qty_reserve"),'0',"qty_reserve") as PBSSOLineQTYReserved;
SQL SELECT
doc_num,
lineno_doc,
qty_reserve
FROM pub.reserve;
Hi Fabrizo,
Try like this,
PBSSOrdersActive_Temp:
Load
"s_order" as PBSSOrder,
"ent_code_ware" as PBSSOrderWHEnt;
SQL SELECT
s_order,
ent_code_ware
FROM pub.sorder where sorder.so_status = 2 AND sorder.ent_code_ware IN ('hc','HC');
Left JOIN (PBSSOrdersActive)
PBSSOrdLine:
Load
"s_order" as PBSSOrder,
SKU as PBSSKU,
lineno as PBSSOLineLineNo,
"qty_onorder" as PBSSOLineQTYonOrder,
"qty_picking" as PBSSOLineQTYinPicking;
SQL SELECT
s_order,
SKU,
lineno,
qty_onorder,
qty_picking
FROM pub.sordline;
Left Join (PBSSOrdersActive)
PBSReserve:
Load
"doc_num" as PBSSOrder,
"lineno_doc" as PBSSOLineLineNo,
"qty_reserve";
SQL SELECT
doc_num,
lineno_doc,
qty_reserve
FROM pub.reserve;
PBSSOrdersActive:
Load *,
If(Isnull("qty_reserve"),0, "qty_reserve") as PBSSOLineQTYReserved
Resident PBSSOrdersActive_Temp;
DROP Table PBSSOrdersActive_Temp;
Instead of Left join, Try Apply Map.
MapTable:
Mapping Load
SOrder# &'-'& Line# as Key,
"Reservation Qty"
From Table B;
[Table A+B]:
Load *,
ApplyMap('MapTable',SOrder# &'-'& Line#,0) as "Reservation Qty"
From Table A;
Hi Fabrizo,
Try like this,
PBSSOrdersActive_Temp:
Load
"s_order" as PBSSOrder,
"ent_code_ware" as PBSSOrderWHEnt;
SQL SELECT
s_order,
ent_code_ware
FROM pub.sorder where sorder.so_status = 2 AND sorder.ent_code_ware IN ('hc','HC');
Left JOIN (PBSSOrdersActive)
PBSSOrdLine:
Load
"s_order" as PBSSOrder,
SKU as PBSSKU,
lineno as PBSSOLineLineNo,
"qty_onorder" as PBSSOLineQTYonOrder,
"qty_picking" as PBSSOLineQTYinPicking;
SQL SELECT
s_order,
SKU,
lineno,
qty_onorder,
qty_picking
FROM pub.sordline;
Left Join (PBSSOrdersActive)
PBSReserve:
Load
"doc_num" as PBSSOrder,
"lineno_doc" as PBSSOLineLineNo,
"qty_reserve";
SQL SELECT
doc_num,
lineno_doc,
qty_reserve
FROM pub.reserve;
PBSSOrdersActive:
Load *,
If(Isnull("qty_reserve"),0, "qty_reserve") as PBSSOLineQTYReserved
Resident PBSSOrdersActive_Temp;
DROP Table PBSSOrdersActive_Temp;
thanks Tamil,
wow, that worked straight away...
it is a bit of a round about way, but gives me the result I need...
so does this mean that the isNull should be done separately to a join?
Thanks again...
Fab
Thanks Shradda,
I will look into the apply map function ... I have never used it before, sounds like it's definitely worth knowing...
Thanks for your contribution on this...
Fab
Fab,
No problem. If the data (PBSSOrder) which is available in the first table (PBSSOrdersActive_Temp) is not present in the second table (PBSReserve) which is to be joined then null values will be present. So we are using resident table to replace the null values with 0 (using if condition).
Below method is not elegant but thought of sharing with you.
PBSSOrdersActive_Temp:
Load
"s_order" as PBSSOrder,
"ent_code_ware" as PBSSOrderWHEnt;
SQL SELECT
s_order,
ent_code_ware
FROM pub.sorder where sorder.so_status = 2 AND sorder.ent_code_ware IN ('hc','HC');
Left JOIN (PBSSOrdersActive)
PBSSOrdLine:
Load
"s_order" as PBSSOrder,
SKU as PBSSKU,
lineno as PBSSOLineLineNo,
"qty_onorder" as PBSSOLineQTYonOrder,
"qty_picking" as PBSSOLineQTYinPicking;
SQL SELECT
s_order,
SKU,
lineno,
qty_onorder,
qty_picking
FROM pub.sordline;
Left Join (PBSSOrdersActive)
PBSReserve:
Load
"doc_num" as PBSSOrder,
"lineno_doc" as PBSSOLineLineNo,
"qty_reserve";
SQL SELECT
doc_num,
lineno_doc,
qty_reserve
FROM pub.reserve;
NullAsValue "qty_reserve"; // You can add more fields but only mentioned fields will be replaced with 0
Set NullValue = 0;
PBSSOrdersActive:
Load *
Resident PBSSOrdersActive_Temp;
DROP Table PBSSOrdersActive_Temp;