Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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 (2)
4 Replies
Highlighted
Specialist III
Specialist III

try with CrossTable

Channa
Highlighted
Contributor III
Contributor III

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

Highlighted
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.

Highlighted
Contributor III
Contributor III

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.