topic Table not being created in QlikView

Table not being created

Anonymous — Wed, 08 Aug 2018 14:37:21 GMT

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!!

Re: Table not being created

agrawalpavan — Wed, 08 Aug 2018 19:21:15 GMT

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.

Re: Table not being created

vishsaggi — Wed, 08 Aug 2018 19:38:33 GMT

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

Re: Table not being created

eduardo_dimperio — Wed, 08 Aug 2018 21:10:04 GMT

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

Re: Table not being created

Anonymous — Thu, 09 Aug 2018 06:13:14 GMT

Thanks for the answer!

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

Re: Table not being created

Anonymous — Thu, 09 Aug 2018 06:15:03 GMT

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!

Re: Table not being created

Anonymous — Thu, 09 Aug 2018 06:16:24 GMT

Thanks for the answer!

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

Re: Table not being created

eduardo_dimperio — Thu, 09 Aug 2018 12:43:13 GMT

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.