Calculate days of delivery, with LOAD...WHILE intruction

[Anonymous]

Hi all,

I would calculate for every ID_BOLLA how many days there are beetween the customer's request date (DATA_RICHIESTA_CONS_AL) and receiving order date (DATA_RX ). Below my code (it doesn't work of course):

Table1:

LOAD

          ID_BOLLA,

          DATA_RICHIESTA_CONS_AL  

resident BOLLE;

Table2:

load

          ID_BOLLA,

          DATA_RX + ITERNO()-1 AS Data,

          1 AS NUM_GG

RESIDENT ARRIVO_TP;

WHILE DATA_RX + ITERNO()-1 < DATA_RICHIESTA_CONS_AL

the code below it works, but the difference is that the 2 dates are on same table:

Table:

LOAD

          ID_BOLLA,

          DATA_RX + ITERNO()-1 AS Data,

          1 AS NUM_GG

RESIDENT BOLLE

WHILE DATA_RX + ITERNO()-1 < DATA_RICHIESTA_CONS_AL;

Please help, thanks in advance.

Best regards.

[Miguel_Angel_Baeyens · Accepted solution]

Hi Fabio,

So you want to get the value from Table1 to Table2, right? Check the following script with mapping tables, to get first table value and put into the second:

Table1Map:
MAPPING LOAD
     ID_BOLLA,
     DATA_RICHIESTA_CONS_AL  
RESIDENT BOLLE;
Table2:
LOAD
     ID_BOLLA,
     ApplyMap('Table1Map', ID_BOLLA) - DATA_RX AS Data, // This equals to DATA_RICHIESTA_CONS_AL - DATA_RX 
     1 AS NUM_GG
RESIDENT ARRIVO_TP;

Hope that helps.

Miguel

[Anonymous]

Hi Miguel,

thank you very much, brilliant solution! But I think it's better if I include the entire script:

Table1Map:

MAPPING LOAD

     ID_BOLLA,

     DATA_RICHIESTA_CONS_AL 

RESIDENT BOLLE;

gg_disponibili_tp: //it means "available days for the delivery"

LOAD

     ID_BOLLA,

     ApplyMap('Table1Map', ID_BOLLA) - DATA_RX AS Data, // This equals to DATA_RICHIESTA_CONS_AL - DATA_RX

     1 AS NUM_GG

RESIDENT ARRIVO_TP;

//---------------------------

left join

LOAD Festività,

     Data

FROM (ooxml, embedded labels, table is vacanze); // this contain a list of holidays  days that will be excluded from the calculation

GIORNI_DISPONIBILI_TP:

LOAD

          ID_BOLLA,

          SUM(NUM_GG) AS GG_DISPONIBILE_TP

RESIDENT gg_disponibili_tp

WHERE ISNULL(Festività) AND WEEKDAY(Data)<5  //this exclude holidays and week end

GROUP BY ID_BOLLA;

drop table gg_disponibili_tp;