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;
