Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
markobanjanin
Contributor III
Contributor III

Qlik script - concatenated excel

Hi,

i have an excel file that I need to parse and load in a qvd. The issue with the excel file is it’s structure. 

First row contains user ids and pivoted days on a month (1 - 31). These are table headers  

say I have 10 users.

2nd row would display month name and then from the 3rd to 12th row I’d get the list of users and their daily sales. 13th row would display next months name and 14-23rd the list of users with daily sales. And so forth. 

How can I parse this file to bring it to tabluar structure?

thank you. 

Labels (1)
4 Replies
Channa
Specialist III
Specialist III

try with CrossTable

Channa
markobanjanin
Contributor III
Contributor III
Author

I’m afraid it won’t work since my id column carries user ids and month names

rogerpegler
Creator II
Creator II

I think you need to process it in the load script in chunks.  

Load it all into a staging table. Then load just the January ID rows ie 3 to 12 into it's own table which can then be processed via the cross table function. The repeats for Feb etc.

How you identify the subsets of rows and loop through the data will depend on the Excel structure. If it's a fixed number of rows, you might be able to effectively hard code the rows. If you need flexibility, perhaps using inter-record functions such as previous can be used.

markobanjanin
Contributor III
Contributor III
Author

I like that approach. Would you further elaborating on the previous function? Attached is an example - rows can grow from one month to the other.