Hi Guys,
I am trying to loop through files and left join with other files from a different location with the same date (each file has a date on the filenames, e.g: Asset Weight_20220706.csv. ).
The challenge I am facing is that somehow qlikview put one date ( the initial date) in each file. Please assist
Here's my code:
ASSET_WEIGHT_DT:
LOAD distinct left(right(FileName(),12),8) as FILE_DT
FROM
[\\fridbk01\FRID_DEV\Landing\LND_FINARCH\Business\NewAssetWeights\Asset Mix\Asset Mix_*.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);
MAX_NUMBER:
LOAD
MAX(RECNO()) AS MAX_REP
RESIDENT ASSET_WEIGHT_DT;
LET DMAX = peek('MAX_REP', 0, 'MAX_NUMBER'); // get max number of file and use that number as a counter
DROP TABLES ASSET_WEIGHT_DT, MAX_NUMBER;
)
FOR i=0 TO '$(DMAX)'-1
//GET FILE DATES
Date:
LOAD distinct left(right(FileName(),12),8) as FILE_DT
FROM
[\\fridbk01\FRID_DEV\Landing\LND_FINARCH\Business\NewAssetWeights\Asset Mix\Asset Mix_*.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);
LET USE_DT = peek('FILE_DT',i, 'Date');
//DROP TABLE Date;
//
ASSET_WEIGHTS:
LOAD
*,
IF([New_Interfund]='SG_SMOOTH_GROSS_R','RM74',[New_Interfund]) AS New_Interfund_1;
LOAD
[P Status],
DATE#('$(USE_DT)', 'YYYY-MM-DD') AS DATE,
[Pcontrol Code],
[Interfund Code],
Weight,
Source,
[Price Run Type],
IF([Interfund Code] = 'SG_SMOOTH_GROSS', 'SG_SMOOTH_GROSS_'&LEFT([Pcontrol Code],1),[Interfund Code]) AS [New_Interfund]
FROM
[\\fridbk01\FRID_DEV\Landing\LND_FINARCH\Business\NewAssetWeights\Asset Weights\Asset Weights_$(USE_DT).csv]
(txt, utf8, embedded labels, delimiter is ',', msq);
Left Join
LOAD [P Status],
DATE#('$(USE_DT)', 'YYYY-MM-DD') AS DATE,
[Pcontrol Code] as New_Interfund_1,
[Interfund Code] as IFC,
Weight AS New_Weight
FROM
[\\fridbk01\FRID_FS\Landing\LND_FINARCH\Business\NewAssetWeights\SG_AW_$(USE_DT).csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);
//get new weight
//combine interfund fields.
ASSET_WEIGHTS_1:
LOAD
[P Status],
DATE,
[Pcontrol Code],
if(New_Interfund_1='RM74', IFC, New_Interfund_1) as [Interfund Code],
Weight,
Source,
[Price Run Type];
LOAD
[P Status],
DATE,
[Pcontrol Code],
IFC,
if(New_Interfund_1='RM74', Weight*New_Weight, Weight) as Weight,
New_Interfund_1,
Source,
[Price Run Type]
RESIDENT ASSET_WEIGHTS;
STORE ASSET_WEIGHTS_1 INTO \\fridbk01\FRID_DEV\Landing\LND_FINARCH\Business\NewAssetWeights\New_Asset_Weights\Asset Weights_'$(USE_DT)'.csv (TXT);
//DROP TABLES ASSET_WEIGHTS,ASSET_WEIGHTS_1;
NEXT
