topic Re: Storing QVD's with filebase name in QlikView

Storing QVD's with filebase name

Thu, 11 Sep 2014 07:23:04 GMT

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

Re: Storing QVD's with filebase name

rubenmarin — Thu, 11 Sep 2014 07:43:23 GMT

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;

I didn't tested last code, maybe there are typos and some bug but that's the idea.

Hope this helps.

Re: Storing QVD's with filebase name

Thu, 11 Sep 2014 07:51:35 GMT

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

Re: Re: Storing QVD's with filebase name

rubenmarin — Thu, 11 Sep 2014 08:01:59 GMT

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?