Adding month to the Variable

[qlikviewforum]

I have a variable called zDates='2016_07'. I need to check whether QVD's with the date already exists in appropriate location or not.

Name of the QVD will be as below.

Employee_M.2016_05.qvd
Employee_M.2016_06.qvd

In this case since 2016_07 qvd does not exists in the file system I need to add that date to the variable. So the variable now should have zDates='2016_07','2016_06'.

If the value in the variable is zDates='2016_06' then there wont be any changes in the varaible it will remain same. That is zDates='2016_06'

If the file system contains below qvd's and the variable zDates='2016_01' then in that case the variable should be updated to zDates='2015_12',2016_01'

Employee_M.2016_11.qvd

Employee_M.2016_12.qvd

Can someone help me to achieve this?

[qlikviewforum]

In short below is what I need. Can anyone help me out on this? As this is very urgent.

If the value in the variable is 2016_07 and if 2016_07 qvd doesn't exists then I should add 2016_06 to the variable.

If the value in the variable is 2016_07 and if 2016_07 qvd  exists then I should not anything to the variable.

if the value in the variable is 2016_01 and 2016_01 doesnt exists then I should add 2015_12 to the variable.

if the value in the variable is 2016_01 and 2016_01 exists then I should not add anything to the variable.

Please let me know if you have any questions.

[adamdavi3s]

The steps I would take are:

1- Scan all files and load the filenames (this code is easily found on here)

2- figure out the numeric code of the filename

3- Then apply your logic from that

something like this, you can tweak the logic to be what you want

//loop through files and load filenames

for each File1 in filelist ('c:\blah\*.qvd')

files:

LOAD keepchar(filename,0123456789) as filecode;

LOAD

     '$(File1)' as filename

   AUTOGENERATE 1;

NEXT File1;

//now pull the max filename from the list

MAX:

LOAD max(filecode) as filecodemax

RESIDENT files;

//drop the table we don't need anymore

DROP TABLE files;

//set our test variable to be the max value

LET zTest = FieldValue('filecodemax',1);

//do the test

let zDates = if(replace('$(zDates)','_','') = $(zTest), //is the max value equal to existing variable?

  left('$(zDates)',4)&'_'&right('$(zDates)',2), //if so then leave it alone

  left($(zTest),4)&'_'&right($(zTest),2)); //otherwise set to max value

Please remember to mark this as helpful or the correct answer if I have helped you or answered your question.

[qlikviewforum]

When i use same piece code it doesn't seem to be working here. I am getting now in filecodemax column not sure why. Also not sure why the value in the filename is prefixed with 00640100250.

[adamdavi3s]

I think you'll have to provide some more information.

Can you provide the exact names of your qvd files as I tested with the ones you gave and it seemed to work fine

[qlikviewforum]

CVAR_M.2016_01.qvd this is the fine name. Can check with this?

[adamdavi3s]

hmm seems to run OK for me:

files:

2016-09-21 08:25:37 0028   LOAD keepchar(filename,0123456789) as filecode

2016-09-21 08:25:37 0029   LOAD

2016-09-21 08:25:37 0030        'c:\blah\CVAR_M.2016_01.qvd' as filename

2016-09-21 08:25:37 0031    AUTOGENERATE 1

2016-09-21 08:25:37         1 fields found: filecode,

2016-09-21 08:25:37        1 lines fetched

2016-09-21 08:25:37 0033 NEXT File1

2016-09-21 08:25:37 0027   files:

2016-09-21 08:25:37 0028   LOAD keepchar(filename,0123456789) as filecode

2016-09-21 08:25:37 0029   LOAD

2016-09-21 08:25:37 0030        'c:\blah\CVAR_M.2016_02.qvd' as filename

2016-09-21 08:25:37 0031    AUTOGENERATE 1

2016-09-21 08:25:37         1 fields found: filecode,

2016-09-21 08:25:37        2 lines fetched

2016-09-21 08:25:37 0033 NEXT File1

2016-09-21 08:25:37 0037 MAX:

2016-09-21 08:25:37 0038 LOAD max(filecode) as filecodemax

2016-09-21 08:25:37 0039 RESIDENT files

2016-09-21 08:25:37       1 fields found: filecodemax,

2016-09-21 08:25:37      1 lines fetched

2016-09-21 08:25:37 0042 DROP TABLE files

2016-09-21 08:25:37 0045 LET zTest = FieldValue('filecodemax',1)

2016-09-21 08:25:37 0049 let zDates = if(replace('2016_01','_','') = 201602,

2016-09-21 08:25:37 0050 left('2016_01',4)&'_'&right('2016_01',2),

2016-09-21 08:25:37 0051 left(201602,4)&'_'&right(201602,2))

2016-09-21 08:25:37      Execution finished.

[qlikviewforum]

I got the issue below numbers existing in the physical file path so it is fetching those numbers as well.

Any idea how can I avoid this?

[qlikviewforum]

We need to use right('$(File1)',6) as filename. Will check and let you know.

[qlikviewforum]

sorry I think we need to use right(keepchar(filename,0123456789),6). I will check and let you know.