topic Re: Scripting Issue in QlikView

Scripting Issue

Tue, 16 Dec 2014 08:02:25 GMT

I have a text file which contains some file names. There is a folder which contains many files. I have to load only those files into my application whose file names matches with the filenames present in the text file. Is there any way by whcih we can do it?

Re: Scripting Issue

datanibbler — Tue, 16 Dec 2014 08:07:55 GMT

Hi,

sure you can. Start with just one file and then, when that works, build the loop to do it:

- Load the txt file

- Get the first record out of it

- Create a variable with the filename (use PEEK())

- Load from that folder with the variable (first load it normally and then replace the file_name with that variable with the $())

HTH

Best regards,

DataNibbler

Re: Scripting Issue

Tue, 16 Dec 2014 08:42:59 GMT

Hi,

Thanks for such a quick response!!

My code has worked fine when i tried the steps which you mentioned for just the first record.

Now when i am looping thorugh all the files, how do i change the value of the variable which stores the filename everytime?

Re: Scripting Issue

jagan — Tue, 16 Dec 2014 08:47:06 GMT

Hi,

Try this script

TableList:

LOAD DISTINCT "TABLE_NAME"

FROM FileName;

Let vTableCount = NoOfRows('TableList');

For i = 0 To $(vTableCount) -1

LET vMyTableName = '' & Peek('TableNames', $(i), 'TableList') & ']';

LET vMyTableNameQVD = Replace(Peek('TableNames', $(i), 'TableList'), ' ', '');

$(vMyTableNameQVD):

LOAD *FROM $(vMyTableName);

STORE $(vMyTableNameQVD) INTO $(ExtractQVD)$(vMyTableNameQVD).QVD;

DROP Table $(vMyTableNameQVD);

Next i

Hope this helps you.

Regards,

Jagan.

Re: Scripting Issue

datanibbler — Tue, 16 Dec 2014 08:47:27 GMT

Hi,

you use a FOR_TO loop, right?

Then you have a counter_variable - I'll assume the default i

=> Just make $(i) (the number) a part of the variable_name.

=> That way, you'll get a lot of variables, so set them to zero at the end: >> SET [variable] = ; << (another loop)

Then you'll have to load the files in that folder in a loop, using the same counter.

If the files are not all equal in structure, you'll have to use the * in the LOAD to just load all there is.

Re: Scripting Issue

Tue, 16 Dec 2014 10:47:28 GMT

Hi,

I am using the code below:

But in this code evertime it is loading only the first record present in the txt file.

SET

ErrorMode=1;
Let vYear=Year(Today());
LET vLast2Years=Year(Today())-2;
/***** Loading FileNames and File Timestamps of all the files *****/
FileDetails:
LOAD [File Name]
From \\Users\Desktop\Test\test.txt(txt);

LET vTotalRowCount=NoOfRows('FileDetails');
FOR each sFileName in filelist ('\\Users\Desktop\Test\*.csv')
For i = 0 To $(vTotalRowCount) -1
LET vFirstValue= Peek('[File Name]', 0, 'FileDetails');
BIS_Temp:
LOAD
*
FROM
[\\Users\Desktop\Test\$(vFirstValue)](txt, codepage is 1252, embedded labels, delimiter is ',', msq)
;
NEXT i
NEXT sFileName
DROP Table FileDetails;

Can you please help?

Re: Scripting Issue

Tue, 16 Dec 2014 14:20:33 GMT

Hi All,

I finally figured out the problem in my code. It should be the variable name instead of 0. That is why it was taking only the first record everytime.

LET vFirstValue= Peek('[File Name]', $(i), 'FileDetails');