Loading the two fields as same field from csv.

[qlikviewforum]

Hi All,

I have following requirement. I got stuck in one point. Can someone help me out for that?

1. All the csv in the folder starts with PRISM(EMPLOYEE_CMO_2012_09,EMPLOYEE_Markets_2013_02,EMPLOYEE_City_2013_09...).For your information all the file ends with YYYY_MM.
2. As discussed there is a change in the field name from 2013_08. So whatever the file which has been received from 2013_08 will have different new name. All the files which was removed before 2013_08 will have old field name.
3. So while loading data from the new file (> 2013_07) it should load with the new fields and while loading from the data from the file (<= 2013_07) it should load with the old field name only.
----------------------------
This is the code which I have written to scan the files from a folder. But got stuck while loading csv files based the condition.

For each vFile in FileList('C:\QlikView\Source\EMPLOYEE_*.csv')
Files: // reading csv files with yearmonth in filename
LOAD *,
right(subfield(FileName, '.' ,1),7) as YearMonth
LOAD '$(vFile)' as FilePath,
subfield('$(vFile)','\',-1) as FileName
autogenerate 1;

if(File name contains >= 2013_08 it should load with new fields) // What should be the condition here???

TableName:
EmpName as CustName,
EmpId as CustId,
field1,
field2
from
C:\QlikView\Source\EMPLOYEE_*.csv //How should I pass the filename here???

else

CustName as CustName,
CustId as CustId,
field1,
field2
from
C:\QlikView\Source\EMPLOYEE_*.csv //How should I pass the filename here???

end if

NEXT vFile

[Clever_Anjos]

For each vFile in FileList('C:\QlikView\Source\EMPLOYEE_*.csv')

Files: // reading csv files with yearmonth in filename

LOAD

  '$(vFile)' as FilePath,

  subfield('$(vFile)','\',-1) as FileName

autogenerate 1;

LET vFileName = right(Peek('FileName'),7);

drop Table Files;

if '$(vFileName)' follows '2013_07' then

TableName:

LOAD

EmpName as CustName,

EmpId as CustId,

field1,

field2

from $(vFile);

else

  another load here...

endif

[qlikviewforum]

Thank you. As I dont have the system with me now. I will try this once I get the system and update you on that. But I am not sure how the below IF statement works? Can you please elaborate this to me.

if '$(vFileName)' follows '2013_07' then

[Clever_Anjos]

Follows is almost like '>'  to strings

So 2013_01 follows 2012_12

2013_09 follows 2013_01

I could convert the string to a number before the comparision, but I chose to write less lines of code

[qlikviewforum]

Does the below If statement will work as it is? I have never used this before . If it works as it is then it is great.

if '$(vFileName)' follows '2013_07' then

[Clever_Anjos]

You only have to assure that all your filenames ends with

EMPLOYEE_CMO_YYYY_MM, so right(filename,7) will get a string like 2013_09, 2013_10 and so on...

[qlikviewforum]

Yes surely the file name will end with YYYY_MM. I will try this and will keep you updated on that. Thank you so much for your help!

[qlikviewforum]

What will be the change in below IF statement if I want to load latest 2 months of data. Please advice.

//if '$(vFileName)' follows '2013_07' then

[#9]

Hi,

Your scenario is very well handled by the use of If condition. This is the best way provided by Qlikview that we can use If condition very robustly!!!

I have added few lines in your script and provided comments so that you can put your appropriate file name in the right place. Just checkthis method if it works for you and let me know. 

P.S. : If your Old file consists  of different set of columns which you wanted to include in the new file format then that is also possible. Just try it and if you need any more information then let me know.

Thanks!!

Regards,

Yojas

Script :

For each vFile in FileList('C:\QlikView\Source\EMPLOYEE_*.csv')

Files: // reading csv files with yearmonth in filename

LOAD *,

right(subfield(FileName, '.' ,1),7) as YearMonth

LOAD '$(vFile)' as FilePath,

subfield('$(vFile)','\',-1) as FileName

autogenerate 1;
//if(File name contains >= 2013_08 it should load with new fields) // What
should be the //condition here???

***   Newly Added Line *** If(right(FileName,7)>=2013_08)

***   Newly Added Line *** Then

TableName:

Load *
from

***   Newly Added Line *** C:\QlikView\Source\(PUT_OLD_FILEFORMAT_HERA)_*.csv
//How should I pass the filename here???

***   Newly Added Line
*** ElseIf(right(FileName,7)>=2013_08) 

Then

LOAD *
from
***   Newly Added Line *** C:\QlikView\Source\(PUT_NEW_FILEFORMAT_HERE)_*.csv //How
should I pass the filename here???

end if
NEXT vFile

[qlikviewforum]

New file format is already taken care. I want to know to just the below. Same thing which I have asked in my previous post. (i,e) Everytime when it loads it should load latest two file only. I mean to say if Jan-2013 is the latest it should load Jan-2013 and Dec-2012 only. Hope it is understandable.

What will be the change in below IF statement if I want to load latest 2 months of data. Please advice.

//if '$(vFileName)' follows '2013_07' then