Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Friends,
Hope all are doing good. I have a excel files named US_DataUSLastmonth.Xls & US_DataUSMTD.xls, IND_DataINDlastmonth.xls & IND_DataINDMTD.xls, UK_DataUKlastmonth.xls & UK_DataUKMTD.xls etc., here i am doing conditional load. Because for monthstart i have to load last monthdata and MTD other wise load MTD load only to concatenate with old QVD. Please find below my Script
if Today()=monthstart(today()) then
Data:
load *,
subfield(filebasename(),'_',1) as Region_Code
from *_Data*lastmonth.xls;
concatenate
load *,
subfield(filebasename(),'_',1) as Region_Code
from *_Data*MTD.xls;
else
load *,
subfield(filebasename(),'_',1) as Region_Code
from *_Data*MTD.xls;
endif;
concatenate
load *,
subfield(filebasename(),'_',1) as Region_Code
from Extraction.qvd
where not exists(Primarykey);
Let VRegion_Code=subfield(filebasename(),'_',1);
store Data into $(VRegion_Code)_Data.qvd;
here I want to load region wise QVD (i.e., US_Data.qvd,UK_Data.qvd) but my script didnt get the proper result. Can anyone help me. Thanks in advance
Hi Nirmal,
Table 'Data' has all the values and different Region_Code values, to create a qvd for each region_code you can do a bucle that extracts the data from Data table and then create the qvd.
Something like:
For i=1 to FieldValueCount('Region_Code')
LET vRegionCode = FieldValue('Region_Code', $(i));
Data_tmp:
NoConcatenate LOAD * From Data Where Region_Code = '$(vRegionCode)';
Store Data_tmp into $(vRegionCode)_Data.qvd;
DROP Table Data_tmp;
Next
I didn't tested last code, maybe there are typos and some bug but that's the idea.
Hope this helps.
Hi Ruben,
Thank you for your valuable response. I already tried that above script. But it is working in Normal load not working in Conditional load.
Thanks
I don't understand what you mean whith 'conditional load', you have to replace your code:
Let VRegion_Code=subfield(filebasename(),'_',1);
store Data into $(VRegion_Code)_Data.qvd;
By the code I provided (or an adaptation of that code)
Just guessing... maybe code fails beacuse in the 'else' instance tha table is unnamed?