Very Strange Bug issue on script

Anonymous — Sun, 23 Apr 2017 04:27:05 GMT

Hi , as much as I would like to give a sample file on this , but is quite difficult as there are quite a few joining and resident tables in this model.

An overview of this model is that it will have the snapshot data of current warehouse, it will then append and store as the days progress. (see last section of the model) on this.

However, all of the fields are stored except those code (section) with the comment //////////////////Not storing////////////////////

Somehow these does not store historical data but the rest do.

Does anybody spot anything wrong on the code?

DAILYKPI:

NoConcatenate

LOAD

Num(Today(1))&'+'&COUNTRY&'+'&FBLFlag&'+'&FK_CURRENT_WAREHOUSE as SKey,

Num(Today(1)) as SnapshotDate,

COUNTRY,

Flag,

FK_CURRENT_WAREHOUSE,

Count(if(FK_INVENTORY_STATUS = 9 and UPDATED_AT < (Today(1)),ID_INVENTORY)) as ITEMS_LOST_DAILY

Resident

DAILY_INVENTORY

Where Match(FK_INVENTORY_STATUS,1,10,11,2,3,8,9,16,17,4,32)

Group By

COUNTRY,

FBLFlag,

FK_CURRENT_WAREHOUSE;

DAILYIVONE:

NoConcatenate

LOAD

CLIENT_ID,

COUNTRY,

ID_INVENTORY,

FK_CURRENT_WAREHOUSE,

FBLFlag,

Floor(InvCreatedAt) as InvCreatedAt

Resident

DAILY_INVENTORY

Where floor(InvCreatedAt) = (Today(1)-1);

Inner Join

LOAD

CLIENT_ID,

ID_INVENTORY,

floor(HISTORY_CREATED_AT) as InvCreatedAt

Resident

DAILY_HIST

Where HISTORY_CREATED_AT >= (Today(1)-1);

Drop Table DAILY_HIST;

DAILYKPI:

//////////////////Not storing////////////////////

Concatenate(DAILYKPI)

LOAD

Num(Today(1))&'+'&COUNTRY&'+'&FBLFlag&'+'&FK_CURRENT_WAREHOUSE as SKey,

Num(Today(1)) as SnapshotDate,

COUNTRY,

FBLFlag,

FK_CURRENT_WAREHOUSE,

Count(DISTINCT ID_INVENTORY) as DAILY_INBOUND_VOLUME

Resident

DAILYIVONE

Group By

COUNTRY,

FBLFlag,

FK_CURRENT_WAREHOUSE;

Drop Table DAILYIVONE;

DAILYCYC:

NoConcatenate

LOAD COUNTRY,

CREATED_AT,

FK_CYCLE_COUNT_ITEM_STATUS,

FK_INVENTORY,

ID_CYCLE_COUNT_ITEM

FROM

[$(vOPSDMDWHQVDPath)OPS_OMS_CYC_CNT_ITM.qvd]

(qvd)

Where

CREATED_AT < Today(1) and CREATED_AT >= (Today(1)-1) and not IsNull(FK_INVENTORY);

Inner Join

LOAD

COUNTRY,

FK_INVENTORY as XX,

Max(ID_CYCLE_COUNT_ITEM) as ID_CYCLE_COUNT_ITEM,

Count(ID_CYCLE_COUNT_ITEM) as cyclecount_volume

Resident

DAILYCYC

Group By

COUNTRY,

FK_INVENTORY;

Inner Join

LOAD

COUNTRY,

ID_INVENTORY as FK_INVENTORY,

INV_COST,

FK_CURRENT_WAREHOUSE,

FBLFlag

Resident

DAILY_INVENTORY;

Drop table DAILY_INVENTORY;

DAILYKPI:

//////////////////Not storing////////////////////

Concatenate(DAILYKPI)

LOAD

Num(Today(1))&'+'&COUNTRY&'+'&FBLFlag&'+'&FK_CURRENT_WAREHOUSE as SKey,

Num(Today(1)) as SnapshotDate,

COUNTRY,

FBLFlag,

FK_CURRENT_WAREHOUSE,

Sum(cyclecount_volume) as DAILY_CYCLE_COUNT_VOLUME,

Count(if(Match(FK_CYCLE_COUNT_ITEM_STATUS,1,6),FK_INVENTORY)) as DAILY_WH_1_ACCUACY,

Count(ID_CYCLE_COUNT_ITEM) as DAILY_WH_2_ACCUACY,

Count(if(FK_CYCLE_COUNT_ITEM_STATUS=6,ID_CYCLE_COUNT_ITEM)) as DAILY_WH_CYCLE_COUNT_NEW_LOST_ITEM,

Sum(if(FK_CYCLE_COUNT_ITEM_STATUS=6,INV_COST)) as DAILY_WH_CYCLE_COUNT_NEW_LOST

Resident

DAILYCYC

Group By

COUNTRY,

FBLFlag,

FK_CURRENT_WAREHOUSE;

Drop Table DAILYCYC;

//Store AggrInvD into [$(vQVDPath)TestB.qvd](qvd);

IF Not(ISNULL(QvdCreateTime('$(vQVDPath)FACTS_WH_QUALITY_KPI_DAILYTEST.qvd'))) THEN

Concatenate(DAILYKPI)

LOAD

FROM $(vQVDPath)FACTS_WH_QUALITY_KPI_DAILYTEST.qvd (qvd)

Where Not Exists(SKey);

END IF

Store DAILYKPI into [$(vQVDPath)FACTS_WH_QUALITY_KPI_DAILYTEST2.qvd](qvd);

Drop Table DAILYKPI;

Re: Very Strange Bug issue on script

rodjager — Sun, 23 Apr 2017 22:45:38 GMT

Hi Benjamin,

Based on a very quick glance it may be that there is no unique row reference in the dailyKPI table. What I mean is that it may be possible for SKey and the related transactions to have the same value more than once so when you export to a cvd you are only seeing the unique records.

You may want to try creating a unique row key per block that contributes to DAILYKPI. The following post may help to get you started:

RecNo or RowNo?

Also, I am assuming the source tables used for the resident loads are loading in data as expected.

Hope this helps.

Rod

topic Very Strange Bug issue on script in QlikView

Very Strange Bug issue on script

Re: Very Strange Bug issue on script