Load one file and store it in multiple files

[Anonymous]

Hi,

I have used QlikSense to load data from a .csv file and give the columns titles and then I have stored it as a .txt file before. This was no problem. Now however, I have one very large file (.csv) containing data over several months. I need to load the file (.csv), give the columns names and instead of storing the file into one large .txt as before, I would like to store the file into several different .txt-files, one for each month.

Is this possible?

For example:

LOAD

@1 as year

@2 as month

@3 as product

FROM [myfile.csv]

store * from [myfile] into [lib://test/mynewfile.txt]

This one works fine. Now however, I want something like this:

LOAD

@1 as year

@2 as month

@3 as product

FROM [myfile.csv]

store * from [myfile] WHERE year = 2017 into [lib://test/mynewfile1.txt]

store * from [myfile] WHERE year = 2016 into [lib://test/mynewfile2.txt]

....

This obviously does not work, just an example. Is it possible to do something like this?

/Anton

[martinpohl]

while storing, you can't reduce datas, only fields.

but you can load datas from your source in loops

example:

for vCount = 0 to 1   //how many loops

let vYear = (year(today()) - $(vCount));  //start Loop with "today"

myfile:

LOAD

@1 as year

@2 as month

@3 as product

FROM [myfile.csv]

where @1 = $(vYear);                              //Limit load datas

store * from [myfile] into [lib://test/mynewfile_$(vYear).txt]     //store with year in filename

drop table myfile;   //clear ram

next                         //next loop

regards

[Anonymous]

For each file you wish to store, first create an internal table and them store it.  Maybe something this :

Temp :

Load

@1 as year

@2 as month

@3 as product

From [myfile.csv]  // probably needs a lib: connection

2017 :

NoConcatenate

Load

*

resident Temp

where Year = 2017 ;

Store Temp into [lib://test/mynewfile2017.txt]  (txt); ;

Drop Table 2017 ;

2016 :

NoConcatenate

Load

*

resident Temp

where Year = 2016 ;

Store Temp into [lib://test/mynewfile2016.txt]  (txt); ;

Drop Table 2016 ;

Drop Table Temp ;

[script above not tested so may have typo's]

[tamilarasu · Accepted solution]

Hi Anton,

If you want to store the data year wise, you can use below script.

Data:
LOAD Year,
Month,
Product
FROM
[myfile.csv];

Temp_Year:
Load Distinct Year
Resident Data;

FOR i=1 to NoOfRows('Temp_Year')
LET vYear = FieldValue('Year',$(i));

NoConcatenate
Temp_Store:
Load *
Resident Data Where Year = '$(vYear)';

STORE Temp_Store into [lib://test/$(vYear).txt];

DROP Table Temp_Store;

NEXT;

DROP Tables Data, Temp_Year;

If you want to store the data year and month wise, you can try below script.

Data:
LOAD Year,
Month,
Product,

Year & Month as YearMonth
FROM
[myfile.csv];

Temp_YearMonth:
Load Distinct YearMonth
Resident Data;

FOR i=1 to NoOfRows('Temp_YearMonth')
LET vYearMonth = FieldValue('YearMonth',$(i));

NoConcatenate
Temp_Store:
Load *
Resident Data Where YearMonth = '$(vYearMonth)';

DROP Field YearMonth From Temp_Store;

STORE Temp_Store into [lib://test/$(vYearMonth).txt];

DROP Table Temp_Store;
NEXT;

DROP Tables Data, Temp_YearMonth;

Good luck.

[Anonymous]

Thank you guys, all of them actually worked. Considering I have a huge amount of data and this was just a simple example, Tamil's answer resulted in the least amount of code needed to perform the task.

THANKS!