topic Re: Resident LOAD in QlikView

Resident LOAD

qlikviewaf — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

i'm loading the table PendingOrders with 2 left join.

Then i would like to load a new table "OrderPending" using as resident PendingOrders table. The script doesn't go in error, but nothing is loaded. Anyone knows why?

Thank you

PendingOrders:

LOAD

SDKCOO as Pending_Order_Company,

SDDOCO as Pending_Order_Number,

SDDCTO as Pending_Order_Type,

SDLNID as Pending_Order_Line,

ApplyMap('PlanRate', SDCRCD, 'FX-not-found')*Num((If(Match(SDLNTY,'N1','N3','N4') or Match(trim(SDLITM), 'WREXLATE', 'INSTLATE', 'WMTIEXP', 'NONSALESFREIGHT', 'MISCMPEXP', 'WMTICHARGE', 'CONSULTINGCHARGE') and Match(trim(SDOTQY), '') ,0, (If(SDKCOO = '00010' and SDCRCD = 'USD',[SDAEXP],

If(SDKCOO = '00010' and SDCRCD <> 'USD', [SDFEA],

If(SDKCOO = '00032' and SDCRCD = 'EUR', [SDAEXP],

If(SDKCOO = '00032' and SDCRCD <> 'EUR',[SDFEA],

If(SDKCOO = '00040' and SDCRCD = 'EUR', [SDAEXP],

If(SDKCOO = '00040' and SDCRCD <> 'EUR',[SDFEA],

If(SDKCOO = '00042' and SDCRCD = 'GBP', [SDAEXP],

If(SDKCOO = '00042' and SDCRCD <> 'GBP',[SDFEA],

If(SDKCOO = '00046' and SDCRCD = 'EUR', [SDAEXP],

If(SDKCOO = '00046' and SDCRCD <> 'EUR',[SDFEA],

If(SDKCOO = '00015' and SDCRCD = 'CAD', [SDAEXP],

If(SDKCOO = '00015' and SDCRCD <> 'CAD',[SDFEA])))))))))))))/100))) as Pending_ExtendedUSD,

SDSHAN as Pending_ShipTo,

If(Len(Trim(SDIVD))>4,Date(MakeDate(1900+Floor(SDIVD/1000))-1+Mod(SDIVD,1000))) as Pending_Invoice_Date_Num;

SQL SELECT *

FROM JDE90.PRODDTA.F4211

WHERE SDNXTR='620' AND SDKCOO IN ('00010','00046','00032','00040','00042') AND SDDOC<>0;

LEFT JOIN (PendingOrders)

PendingF0101:

LOAD

Country AS Pending_Country,

Address_Number as Pending_ShipTo,

// Territory,

(IF(Match(trim(Cat_Cde), '15'), 'CN', if(Match(trim(Cat_Cde), '80'), 'JP',

// If(Match(Trim(Address_Number), '121306','102057','113804','135714','117204','126492','109083','102056','102039','109892','186958','141575','227535'), 'CO',

If(Match(Trim(Cat_Cde), '99'), 'CH', Cat_Cde)))) as Pending_Region,

Name as Pending_ShipToDescription

FROM C:\ProgramData\QlikTech\Documents\Saved\Address_Book.qvd(qvd);

left join (PendingOrders)

F0005:

Load

trim(DRKY) as Pending_Region,

DRDL02 AS Pending_Region_Description,

DRDL01 AS Pending_Area;

SQL SELECT *

FROM JDE90.PRODCTL.F0005

where DRRT = '01' and DRSY = 'QV';

OrderPending:

LOAD

RESIDENT PendingOrders;

Re: Resident LOAD

YoussefBelloum — Thu, 17 May 2018 09:09:53 GMT

Hi,

Add noconcatenate, like this:

PendingOrders:

LOAD

SDKCOO as Pending_Order_Company,

SDDOCO as Pending_Order_Number,

SDDCTO as Pending_Order_Type,

SDLNID as Pending_Order_Line,

If(SDKCOO = '00010' and SDCRCD <> 'USD', [SDFEA],

If(SDKCOO = '00032' and SDCRCD = 'EUR', [SDAEXP],

If(SDKCOO = '00032' and SDCRCD <> 'EUR',[SDFEA],

If(SDKCOO = '00040' and SDCRCD = 'EUR', [SDAEXP],

If(SDKCOO = '00040' and SDCRCD <> 'EUR',[SDFEA],

If(SDKCOO = '00042' and SDCRCD = 'GBP', [SDAEXP],

If(SDKCOO = '00042' and SDCRCD <> 'GBP',[SDFEA],

If(SDKCOO = '00046' and SDCRCD = 'EUR', [SDAEXP],

If(SDKCOO = '00046' and SDCRCD <> 'EUR',[SDFEA],

If(SDKCOO = '00015' and SDCRCD = 'CAD', [SDAEXP],

If(SDKCOO = '00015' and SDCRCD <> 'CAD',[SDFEA])))))))))))))/100))) as Pending_ExtendedUSD,

SDSHAN as Pending_ShipTo,

If(Len(Trim(SDIVD))>4,Date(MakeDate(1900+Floor(SDIVD/1000))-1+Mod(SDIVD,1000))) as Pending_Invoice_Date_Num;

SQL SELECT *

FROM JDE90.PRODDTA.F4211

WHERE SDNXTR='620' AND SDKCOO IN ('00010','00046','00032','00040','00042') AND SDDOC<>0;

LEFT JOIN (PendingOrders)

PendingF0101:

LOAD

Country AS Pending_Country,

Address_Number as Pending_ShipTo,

// Territory,

(IF(Match(trim(Cat_Cde), '15'), 'CN', if(Match(trim(Cat_Cde), '80'), 'JP',

// If(Match(Trim(Address_Number), '121306','102057','113804','135714','117204','126492','109083','102056','102039','109892','186958','141575','227535'), 'CO',

If(Match(Trim(Cat_Cde), '99'), 'CH', Cat_Cde)))) as Pending_Region,

Name as Pending_ShipToDescription

FROM C:\ProgramData\QlikTech\Documents\Saved\Address_Book.qvd(qvd);

left join (PendingOrders)

F0005:

Load

trim(DRKY) as Pending_Region,

DRDL02 AS Pending_Region_Description,

DRDL01 AS Pending_Area;

SQL SELECT *

FROM JDE90.PRODCTL.F0005

where DRRT = '01' and DRSY = 'QV';

OrderPending:

NOCONCATENATE

LOAD

RESIDENT PendingOrders;

Re: Resident LOAD

shiveshsingh — Thu, 17 May 2018 09:44:11 GMT

This is happening because your resident table output is getting concatenated to previous table.

Try like below

Noconcatenate

OrderPending:

LOAD

RESIDENT PendingOrders;

OrderPending:

LOAD

* , 'F' as Flag

RESIDENT PendingOrders;