Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikviewforum
Creator II
Creator II

Adding month to the Variable

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?

32 Replies
qlikviewforum
Creator II
Creator II
Author

 

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
Master
Master

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
Creator II
Creator II
Author

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
Master
Master

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
Creator II
Creator II
Author

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

adamdavi3s
Master
Master

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
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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

qlikviewforum
Creator II
Creator II
Author

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