Storing QVD's with filebase name

[#1]

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

[rubenmarin]

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.

[#3]

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

[rubenmarin]

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?