topic Re: Adding month to the Variable in QlikView

Adding month to the Variable

qlikviewforum — Tue, 20 Sep 2016 17:59:41 GMT

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?

Re: Adding month to the Variable

qlikviewforum — Wed, 21 Sep 2016 06:11:21 GMT

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.

Re: Adding month to the Variable

adamdavi3s — Wed, 21 Sep 2016 06:46:20 GMT

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.

Re: Adding month to the Variable

qlikviewforum — Wed, 21 Sep 2016 07:14:18 GMT

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.

Re: Adding month to the Variable

adamdavi3s — Wed, 21 Sep 2016 07:17:29 GMT

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

Re: Adding month to the Variable

qlikviewforum — Wed, 21 Sep 2016 07:22:41 GMT

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

Re: Adding month to the Variable

adamdavi3s — Wed, 21 Sep 2016 07:26:36 GMT

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.

Re: Adding month to the Variable

qlikviewforum — Wed, 21 Sep 2016 07:29:10 GMT

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?

Re: Adding month to the Variable

qlikviewforum — Wed, 21 Sep 2016 07:31:30 GMT

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

Re: Adding month to the Variable

qlikviewforum — Wed, 21 Sep 2016 07:36:36 GMT

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

Re: Adding month to the Variable

adamdavi3s — Wed, 21 Sep 2016 07:41:09 GMT

Cool, hopefully you can just tweak that code until it works!

Re: Adding month to the Variable

qlikviewforum — Wed, 21 Sep 2016 07:50:55 GMT

Yes I am trying to do that. But I am getting the below error not sure why.

Script line error:

let zSecondLoadDates = if(replace('''2016_07'',''2016_09''','_','') = 201607,left('''2016_07'',''2016_09''',4)&'_'&right('''2016_07'',''2016_09''',2),

left(201607,4)&'_'&right(201607,2))

I think value in the variable is something like below

zSecondLoadDates='2016_07'

Any idea how this can be handled?

Re: Adding month to the Variable

adamdavi3s — Wed, 21 Sep 2016 07:56:00 GMT

Hmmm so the variable includes the ' already?

if so then take that out of the replace statement so

if(replace('$(zDates)','_','')

becomes

if(replace($(zDates),'_','')

It looks like you just have too many ' going on!

Re: Adding month to the Variable

qlikviewforum — Wed, 21 Sep 2016 07:56:08 GMT

The variable already contains value like below

zSecondLoadDates='2016_07'

If the new date has to be added then it should be something like below

zSecondLoadDates='2016_07','2016_06'

Not sure how this can be handled

Re: Adding month to the Variable

qlikviewforum — Wed, 21 Sep 2016 07:58:39 GMT

it tried doing this but it didn't work.

Yes too many thing going on. Not been able to focus

Re: Adding month to the Variable

qlikviewforum — Wed, 21 Sep 2016 08:07:57 GMT

Sorry I think I had two values in zSecondLoadDates='2016_07','2016_09' so it was getting failed.

Re: Adding month to the Variable

adamdavi3s — Wed, 21 Sep 2016 08:09:23 GMT

ahhhh yes I can see that now in your error, sorry I am not awake yet!

Re: Adding month to the Variable

qlikviewforum — Wed, 21 Sep 2016 08:09:59 GMT

Sorry this didn't work either

Re: Adding month to the Variable

qlikviewforum — Wed, 21 Sep 2016 08:10:56 GMT

Sorry to disturb you. Please check if you can help on this as it is very urgent.

Re: Adding month to the Variable

adamdavi3s — Wed, 21 Sep 2016 08:12:18 GMT

what error do you get now you only have one item in the variable?