topic Re: Loading the two fields as same field from csv. in QlikView

Loading the two fields as same field from csv.

qlikviewforum — Wed, 25 Sep 2013 17:12:48 GMT

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.

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

Re: Loading the two fields as same field from csv.

Clever_Anjos — Wed, 25 Sep 2013 17:19:09 GMT

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

Re: Loading the two fields as same field from csv.

qlikviewforum — Wed, 25 Sep 2013 17:34:59 GMT

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

Re: Loading the two fields as same field from csv.

Clever_Anjos — Wed, 25 Sep 2013 17:37:56 GMT

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

Re: Loading the two fields as same field from csv.

qlikviewforum — Wed, 25 Sep 2013 17:43:28 GMT

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

Re: Loading the two fields as same field from csv.

Clever_Anjos — Wed, 25 Sep 2013 17:45:39 GMT

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

Re: Loading the two fields as same field from csv.

qlikviewforum — Wed, 25 Sep 2013 17:48:18 GMT

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!

Re: Loading the two fields as same field from csv.

qlikviewforum — Fri, 25 Oct 2013 12:40:01 GMT

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

Re: Loading the two fields as same field from csv.

Fri, 25 Oct 2013 13:04:24 GMT

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

Re: Loading the two fields as same field from csv.

qlikviewforum — Fri, 25 Oct 2013 13:55:45 GMT

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

Re: Loading the two fields as same field from csv.

Clever_Anjos — Sat, 26 Oct 2013 17:41:53 GMT

LET TwoMonths = date(AddMonths(Today(),-2),'YYYY_MM');

//if '$(vFileName)' follows '$(TwoMonths)' then

Re: Loading the two fields as same field from csv.

Clever_Anjos — Sat, 26 Oct 2013 17:42:41 GMT

You should mark this question as answered...

Re: Loading the two fields as same field from csv.

qlikviewforum — Sat, 26 Oct 2013 18:52:13 GMT

Thank you. I will check and update you.

By the way when we are in the starting of the year(Jan-2014), will it load Jan-2014 data and Dec-2013 data? Please answer...

As I don't have the access I couldn't verify that.

Re: Loading the two fields as same field from csv.

qlikviewforum — Sat, 26 Oct 2013 19:11:51 GMT

By the way any idea how can I load latest 2 weeks of data? (i,e Current week and Previous week). Actually I was looking for that also. Also make sure that when we are in the first week of the year it should load first the current year and last week of the previous year as well. Any idea on this? Please help...

Re: Re: Loading the two fields as same field from csv.

Clever_Anjos — Sun, 27 Oct 2013 11:22:01 GMT

In Jan/2014 this variable

LET TwoMonths = date(AddMonths(Today(),-2),'YYYY_MM');

will contain 2013_11 so "follow" operador will work fine

Re: Loading the two fields as same field from csv.

Clever_Anjos — Sun, 27 Oct 2013 11:24:01 GMT

= date(WeekStart(Today())-7,'YYYYMMDD') will return the 1st day of last week, so you can compare

Re: Loading the two fields as same field from csv.

Sun, 27 Oct 2013 17:58:24 GMT

Hi,

I am not sure but can you try something like,

For Each '$(vFileName)' follows all files in your folder(FileName_*).

If vFileName = max(Value in the folder) (eg. if max value is 2013_07) then

Put here your loading script.

else if vFileName = max(Value in the folder)- 1 then

Put here your loading script.

endif

This script will give you only two files because of your if condition in for loop.

Please update if it works, else just share any sample example so that you will get proper o/p.

Regards,

Yojas