Table not being created

[Anonymous]

Hi,

We have two loops for calculating Inbound and Outbound forecasted dates. The loops are needed to get country holildays of every ISO. Also we calculate which ones are shipped by ground or not. So this is the loop code for Inbound dates:

FOR EACH vISOCode in $(vISOCodes)

    // Create temporary variable for holidays of actual ISO_CODE

    COUNTRY_HOLIDAYS_TMP:

    LOAD

        COUNTRY_HOLIDAYS AS CTRY_HD_TMP

    RESIDENT COUNTRY_HOLIDAYS_LIST

    WHERE ISO_CODE='$(vISOCode)';

    LET holidaysTMP = PEEK('CTRY_HD_TMP', -1, 'COUNTRY_HOLIDAYS_TMP');

    DROP TABLE COUNTRY_HOLIDAYS_TMP;

    GROUND:

    LOAD

        ITEM_KEY

        , DATE( LastWorkDate( INBOUND_START, ESTIMATED_INB_TAT + 1, $(holidaysTMP) ) ) AS FC_REPAIR_START

    RESIDENT MAIN_TABLE

    WHERE (ISO_CODE='$(vISOCode)'

    AND IN_SHIP_METHOD = 'UT';

    NOT_GROUND:

    LOAD

        ITEM_KEY

        , DATE( LastWorkDate( INBOUND_START, ESTIMATED_INB_TAT, $(holidaysTMP) ) ) AS FC_REPAIR_START

    RESIDENT MAIN_TABLE

    WHERE ISO_CODE='$(vISOCode)'

    AND IN_SHIP_METHOD <> 'UT');

    // Erase variable

    LET holidaysTMP=;

NEXT vISOCode

After this, we concatenate both tables and drop them. If we debug this code with limited load of 1000 rows, we get 926 rows for GROUND and 74 for NOT_GROUND.

The problem comes with the second one (Outbound:

FOR EACH vISOCode in $(vISOCodes)

    COUNTRY_HOLIDAYS_TMP:

    LOAD

        COUNTRY_HOLIDAYS AS CTRY_HD_TMP

    RESIDENT COUNTRY_HOLIDAYS_LIST

    WHERE ISO_CODE='$(vISOCode)';

    LET holidaysTMP = PEEK('CTRY_HD_TMP', -1, 'COUNTRY_HOLIDAYS_TMP');

    DROP TABLE COUNTRY_HOLIDAYS_TMP;

    GROUND:

    LOAD

        ITEM_KEY

        , DATE( LastWorkDate( EXPECTED_NUR_OUT, CARRIER_OUT_TAT + DELAY_REMOTE_DELIVERY, $(holidaysTMP) ) ) AS FC_OUTBOUND_END

    RESIDENT MAIN_TABLE

    WHERE ISO_CODE='$(vISOCode)'

    AND IN_SHIP_METHOD = 'UT';

    NOT_GROUND:

    LOAD

        ITEM_KEY

        , DATE( LastWorkDate( $(THIS_REPAIR_END), CARRIER_OUT_TAT + 1 + DELAY_REMOTE_DELIVERY, $(holidaysTMP) ) ) AS FC_OUTBOUND_END

    RESIDENT MAIN_TABLE

    WHERE ISO_CODE='$(vISOCode)'

    AND IN_SHIP_METHOD <> 'UT';

    // Erase variable

    LET holidaysTMP=;

NEXT vISOCode

When we execute the same debug as before, we get 1000 for GROUND and NOT_GROUND does not exists! That's strange as they should be the same results as before. I've tryend changing the name of the tables and happens de same. Also, I've tried to insert one row to NOT_GROUND after the loop like this:

NOT_GROUND:

LOAD * INLINE [

   ITEM_KEY, FC_OUTBOUND_END

   DOLZ1979, '28/08/2018'

];

And, in debug, it loads this row to GROUND table!! GROUND table with 1001 rows and NOT_GROUND table not existing!! If I drop GROUND table before the inline load, then NOT_GROUND gets this row. If I change the order of the table loads in the loop, then NOT_GROUND table exists but GROUND not!

Has anyone facing this issue and could throw some light?

Thanks in advance!!

[agrawalpavan]

I am not sure of the entire logic but I have seen an issue where if a table is loaded with exact same number of fields, it does not get loaded.

Try adding fields like these,

1 as Counter1,

2 As Counter2 and so on

to these Loads and see if that solves the issue.

After the loop you can drop these columns using drop statement.

[vishsaggi · Accepted solution]

IF there are same name and number of fields between two tables autoconcatenation happens. May be you want to use like

Ground:

LOAD ....

;

NoConcatenate

Not_Ground:

LOAD ....

;

However doing above will get you synthetic keys.

So try to rename the field names like below

  GROUND:

    LOAD

        ITEM_KEY

        , DATE( LastWorkDate( INBOUND_START, ESTIMATED_INB_TAT + 1, $(holidaysTMP) ) ) AS FC_REPAIR_START

    RESIDENT MAIN_TABLE

    WHERE (ISO_CODE='$(vISOCode)'

    AND IN_SHIP_METHOD = 'UT';

    NOT_GROUND:

    LOAD

        ITEM_KEY

        , DATE( LastWorkDate( INBOUND_START, ESTIMATED_INB_TAT, $(holidaysTMP) ) ) AS FC_REPAIR_START_NC

    RESIDENT MAIN_TABLE

    WHERE ISO_CODE='$(vISOCode)'

    AND IN_SHIP_METHOD <> 'UT');

    // Erase variable

    LET holidaysTMP=;

NEXT vISOCode

[eduardo_dimperio]

For Qlik both tables are the same, chenge fields name, and after loop back to normal

FOR EACH vISOCode in $(vISOCodes)

    COUNTRY_HOLIDAYS_TMP:

    LOAD

        COUNTRY_HOLIDAYS AS CTRY_HD_TMP

    RESIDENT COUNTRY_HOLIDAYS_LIST

    WHERE ISO_CODE='$(vISOCode)';

    LET holidaysTMP = PEEK('CTRY_HD_TMP', -1, 'COUNTRY_HOLIDAYS_TMP');

    DROP TABLE COUNTRY_HOLIDAYS_TMP;

    GROUND:

    LOAD

        ITEM_KEY as GROUND_ITEM_KEY

        , DATE( LastWorkDate( EXPECTED_NUR_OUT, CARRIER_OUT_TAT + DELAY_REMOTE_DELIVERY, $(holidaysTMP) ) ) AS GROUND_FC_OUTBOUND_END

    RESIDENT MAIN_TABLE

    WHERE ISO_CODE='$(vISOCode)'

    AND IN_SHIP_METHOD = 'UT';

    NOT_GROUND:

    LOAD

        ITEM_KEY AS NOT_GROUND_ITEM_KEY

        , DATE( LastWorkDate( $(THIS_REPAIR_END), CARRIER_OUT_TAT + 1 + DELAY_REMOTE_DELIVERY, $(holidaysTMP) ) ) AS NOT_GROUND_FC_OUTBOUND_END

    RESIDENT MAIN_TABLE

    WHERE ISO_CODE='$(vISOCode)'

    AND IN_SHIP_METHOD <> 'UT';

    // Erase variable

    LET holidaysTMP=;

NEXT vISOCode

[Anonymous]

Thanks for the answer!

It was that problem, but I rather prefer to add NoConcatenate ​function.

[Anonymous]

Thank u so much! It was that!!

I like the NoConcatenate function and I don't care about synthtetic keays as I make a concatenation of both tables after that. I'm thinking about not using concatenation at all and put everything at once in the same final table.

Thanks again!

[Anonymous]

Thanks for the answer!

It was that problem, but I rather prefer to add NoConcatenate function.

[eduardo_dimperio]

The problem when you use NoConcatenate in a loop is create several tables, like GROUND, GROUD-1, GROUND-2 etc etc and work with this could be a problem, but you know better then me if in your code is better or not to use NoConcatenate.