Skip to main content

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
beaubellamy
Partner - Contributor III
Partner - Contributor III

Load separate columns in loop

I have a data set that has the date (month and year) as a field with the value of interest in each column for the corresponding date. 

Below is a snapshot of the data file.

image.png

What I would like in the available fields is

Type, Route Number, Route Name, Date,  people

Below is my load script. 

I am only able to load the first column values, but I'm still missing the expected date value (yyyy_mm)

set vmonths = 'January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December', 'January1', 'February1', 'March1', 'April1', 'May1', 'June1';

for each vmonth in $(vmonths)
Trace $(vmonth);

LOAD
"Type",
"Route Number",
"Route Name",


if($(vmonth) = 'January', '2019_01',if($(vmonth) = 'February', '2019_02',if($(vmonth) = 'March', '2019_03',if($(vmonth) = 'April', '2019_04',
if($(vmonth) = 'May', '2019_05',if($(vmonth) = 'June', '2019_06',if($(vmonth) = 'July', '2019_07',if($(vmonth) = 'August', '2019_08',
if($(vmonth) = 'September', '2019_09',if($(vmonth) = 'October', '2019_10',if($(vmonth) = 'November', '2019_11',if($(vmonth) = 'December', '2019_12',
if($(vmonth) = 'January1', '2020_01',if($(vmonth) = 'February1', '2020_02',if($(vmonth) = 'March1', '2020_03',if($(vmonth) = 'April1', '2020_04',
if($(vmonth) = 'May1', '2020_05',if($(vmonth) = 'June1', '2020_06', $(vmonth))))))))))))))))))) as year_month,

$(vmonth) as people

FROM [file];
Next vmonth;

Any help would be appreciated.

Labels (4)
1 Solution

Accepted Solutions
Ksrinivasan
Specialist
Specialist

Hi,

Why not you try to Cross table function.

It will give your required result.

Regards,

Ksrinivasan

View solution in original post

2 Replies
Ksrinivasan
Specialist
Specialist

Hi,

Why not you try to Cross table function.

It will give your required result.

Regards,

Ksrinivasan

beaubellamy
Partner - Contributor III
Partner - Contributor III
Author

I wasn't aware of this function. Thanks its exactly what I need.