Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Load one file and store it in multiple files

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

1 Solution

Accepted Solutions
tamilarasu
Champion
Champion

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.

View solution in original post

4 Replies
martinpohl
Partner - Master
Partner - Master

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
Not applicable
Author

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

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
Not applicable
Author

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!