topic Difference between LET/SET in QlikView

Difference between LET/SET

Wed, 11 Nov 2009 20:56:17 GMT

Hello !

I always confuse myself about using LET or SET or none of them.

Say that I want to create a NEW variable ? Do I use LET or SET ?

What about assigning a value to a variable ? Do I use LET/SET or nothing ?

Thanks !

Difference between LET/SET

Anonymous — Wed, 11 Nov 2009 21:18:37 GMT

It is very simple:

using SET you are adding string value to a variable
SET Variable1='4+5';
variable value is '4+5'

using LET you are adding an expression result to a variable
LET Variable1=4+5;
variable value is 9

Rgds,
Artjoms

Difference between LET/SET

Wed, 11 Nov 2009 21:23:49 GMT

Hi Jabalite ,

The set statement is used for defining script variables. These can be used for substituting strings, paths, drives, etc.

The let statement, in opposition to the set statement, evaluates the expression on the right side of the ' =' before it is assigned to the macro variable.

Set x=3+4;

Let y=3+4

z=$(y)+1;

$(x) will be evaluated as ' 3+4 '

$(y) will be evaluated as ' 7 '

$(z) will be evaluated as ' 8 '

Regards,

Poorva

Difference between LET/SET

Wed, 11 Nov 2009 21:39:35 GMT

Thanks to you all !

Now I understand :

SET is like a # DEFINE in C

and

LET is the typical assignment operator !

Thanks !

Difference between LET/SET

Wed, 11 Nov 2009 21:54:54 GMT

Ops...

I'm confused again...

I have a situation here where several CSV files are available on a folder. I want to pick the LAST file name on the path. Cause the file path has the YEAR/MONTH on it's composition. An example of file name is 'FPCDA_200911.CSV' where 200911 is the year/month of the file.

So I need to pick the last YEAR/MONTH file available on that folder.

Here's how I coded it :


LET LASTFILE='';

FOR EACH AFILENAME IN FILELIST R:\USR\CPD\PRO\GT\01\EB\DADOS\FP\FPCDA\*.QVD;
 IF '$(AFILENAME)' > '$(LASTFILE)' THEN
 SET LASTFILE=$(AFILENAME);
 END IF
NEXT

LET FILENAMETOBEREAD=$(LASTFILE);

LOAD
...
FROM $(FILENAMETOBEREAD)

But now I'm not sure if this is the appropriate coding for that ..

Can you help me ?

Once more, thank you !

Difference between LET/SET

Wed, 11 Nov 2009 21:57:00 GMT

Ops...

I'm confused again...

So I need to pick the last YEAR/MONTH file available on that folder.

Here's how I coded it :


LET LASTFILE='';

FOR EACH AFILENAME IN FILELIST R:\USR\CPD\PRO\GT\01\EB\DADOS\FP\FPCDA\*.QVD;
 IF '$(AFILENAME)' > '$(LASTFILE)' THEN
 SET LASTFILE=$(AFILENAME);
 END IF
NEXT

LET FILENAMETOBEREAD=$(LASTFILE);

LOAD
...
FROM $(FILENAMETOBEREAD)

But now I'm not sure if this is the appropriate coding for that ..

Can you help me ?

Once more, thank you !

Difference between LET/SET

Wed, 11 Nov 2009 22:06:28 GMT

I'm still confused....

I don't understand why the code below does not work.

Say that I have a folder with several CSV files on it. The file names are comprised of a prefix plus YEAR/MONTH of the file. Like this file names list :

R:\USR\CPD\PRO\GT\01\EB\DADOS\FP\FPCDA\FPCDA_200901.CSV
R:\USR\CPD\PRO\GT\01\EB\DADOS\FP\FPCDA\FPCDA_200902.CSV
R:\USR\CPD\PRO\GT\01\EB\DADOS\FP\FPCDA\FPCDA_200903.CSV
R:\USR\CPD\PRO\GT\01\EB\DADOS\FP\FPCDA\FPCDA_200904.CSV
R:\USR\CPD\PRO\GT\01\EB\DADOS\FP\FPCDA\FPCDA_200905.CSV
R:\USR\CPD\PRO\GT\01\EB\DADOS\FP\FPCDA\FPCDA_200906.CSV
R:\USR\CPD\PRO\GT\01\EB\DADOS\FP\FPCDA\FPCDA_200907.CSV
R:\USR\CPD\PRO\GT\01\EB\DADOS\FP\FPCDA\FPCDA_200908.CSV
R:\USR\CPD\PRO\GT\01\EB\DADOS\FP\FPCDA\FPCDA_200909.CSV
R:\USR\CPD\PRO\GT\01\EB\DADOS\FP\FPCDA\FPCDA_200910.CSV

Notice that '200910' is the YEAR/MONTH of the file.

I need to discover the last YEAR/MONTH file on that folder, to read THAT file.

I coded like this :


LET LASTFILE='';

FOR EACH FILE_NAME IN FILELIST R:\USR\CPD\PRO\GT\01\EB\DADOS\FP\FPCDA\*.QVD;
 IF '$(FILE_NAME)' > '$(LASTFILE)' THEN
 LET LASTFILE=$(FILE_NAME);
 END IF
NEXT

LOAD
...
FROM $(LASTFILE)

I dont know why that code does not work...

Thanks !

Difference between LET/SET

Anonymous — Thu, 12 Nov 2009 00:47:19 GMT

What kind of files are you loading then? 'Cause in directory you have CSV file, but in FOR EACH statement you mentioned QVD extension.
First of all it is very important to follow the sintax, then everything will be fine

Secondly, it is hard to compare strings, that's why I involved one more variable var2 which value is number (year and month)

This script is working (tested):


LET var1=0;

For Each File in Filelist ('R:\USR\CPD\PRO\GT\01\EB\DADOS\FP\FPCDA\*.CSV')
 LET var2 = Left(Right('$(File)',10),6);
 If '$(var2)' > '$(var1)' Then
 SET LastFile = '$(File)';
 LET var1 = '$(var2)';
 End If
Next File

Load *
FROM [$(LastFile)] (ansi, txt, delimiter is ',', embedded labels, msq); // for CSV files
//FROM [$(LastFile)] (qvd); // for QVD files

Case you have QVD files change CSV extension to QVD in "For Each..." line, uncomment From line for QVD and comment line for CSV.

Good luck!

Rgds,
Artjoms

Difference between LET/SET

Thu, 12 Nov 2009 00:59:47 GMT

Ops ! Sorry !

The fact is that we have an Unisys mainframe and all tables are downloaded from it as CSV. Right after downloading them, I convert them all to QVD. So, on that folder I have a QVD for each CSV file. That's automatic. Whenever I extract a table from the mainframe this thing happens. I've built this infrastructure already.

As for the string comparison, I understand that it is boring.

However, I wanna a more "generic" code cause there are several filename formats. Sometimes it's YYYYMM, others are YYYYMMDD and yet on others we have simply a number (counter) amongst the file name.

But I'm STILL confused with SET/LET !!!!!!!!!!!!!!!!

You used

SET LASTFILE=

Why not 'LET' in this case as well ?

Funny, I'm a skilled programmer ! Never got messed up by such a silly thing !

But thanks again !

Difference between LET/SET

Anonymous — Thu, 12 Nov 2009 01:28:42 GMT

I used SET LastFile, 'cause I need to assign a simple string to a variable value ('$(File) - is a string)

Probably you have to compare file creation date and time to get latest (last) file. For this QV has a function FileTime

Try this...


Let var1=0;

For Each File in Filelist ('R:\USR\CPD\PRO\GT\01\EB\DADOS\FP\FPCDA\*.QVD')
 If FileTime('$(File)') > '$(var1)' Then
 SET LastFile = '$(File)';
 LET var1=FileTime('$(File)');
 End If
Next File

Load *
FROM [$(LastFile)] (qvd);

Difference between LET/SET

Thu, 12 Nov 2009 01:38:13 GMT

The file CREATION time is irrelevant.

The most important is the FILE NAME.

Cause the file from April/2009 may have been refreshed.

However, the chronological most old file is still November/2009.

So, on my script I do need November/2009, even though April/2009 may have been refresed (re-created).

Got it ?

As for LET/SET, is it correct to say this :

When you need a STRING VARIABLE ASSIGNMENT, use SET

When you need a NUMERICAL VARIABLE ASSIGNMENT, use LET

Is that correct ?

Difference between LET/SET

Anonymous — Thu, 12 Nov 2009 04:08:06 GMT

About LET/SET - not exactly. May be it's also possible to assign a number with SET (SET var=0;), but I haven't tried.
The main LET role is to assign a result of some function or expression to a variable. The result of a function could be also string.

I understood you case with file names. We had a similar problem. It's near impossible to handle all posslible variants of month/date format in file names. That's why in our case we gave a strong recomendation to users to save file in defined format.

May be you can modify analytics what generates QVD files from CSV? If in CSV data is some Date field which can define month and year, then you can save QVD with file name you need. If the QVD name has to be the same as CSV then, as I mentioned, it's very difficult to create some "generic" code for this case.

Rgds,
Artjoms

Difference between LET/SET

Thu, 12 Nov 2009 17:18:58 GMT

>>About LET/SET - not exactly. May be it's also possible to assign a number with SET (SET var=0;), but I haven't tried.
>>The main LET role is to assign a result of some function or expression to a variable. The result of a function could be also string.

OK.

Still dont have a strong clue on how to use one or another...

QV Team could be handy at this point ...

Are you hearing us ?

>>I understood you case with file names. We had a similar problem. It's near impossible to handle all posslible variants of month/date format in file >>names. That's why in our case we gave a strong recomendation to users to save file in defined format.

OK. I'lll think about it.

>>May be you can modify analytics what generates QVD files from CSV? If in CSV data is some Date field which can define month and year, then you >>can save QVD with file name you need. If the QVD name has to be the same as CSV then, as I mentioned, it's very difficult to create some "generic" >>code for this case.

The QVD file name is ALWAYS the same name as the CSV. For each and every CSV we have a QVD.

Maybe , when the file name has some sorting part on it , such as a date or a number, it could be always followed by an underscore or something similar. Then, a generic function could always substr on it.

I'll think about it. Not difficult to standardize since I'm the one who built the mainframe exctraction tool.

Thanks again. You're very kind.

Rgds,
Artjoms

Difference between LET/SET

Anonymous — Thu, 12 Nov 2009 17:27:14 GMT

URW!

Good luck!