Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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]
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.
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!