For each Crosstable

[#1]

Looking for a bit of advice here.

I'm fairly new to Qlikview and have a spreadsheet which is sent to me once a month. The FD would like me to create a dashboard for this and I don't want to redesign his spreadsheet.

I have attached a sample of the spreadsheet and as you can see for each of the headings in column O their is a relevant "table" in column A to M.

'm okay with crosstables and data transformation steps but don't want to do this in the script 20 times or so for each of the headings in column O.

I'm thinking that I should be able to load the headings inline and then do a for each loop on the contents but its how I would then load the data from the spreadsheet without using crosstable loads thats the problem.

I think I'm really asking what is the best way to do this ? any pointers on best practice would be much appreciated.

.

[#2 · Accepted solution]

I found the answer myself.

All it took was a little bit of time to get my head around it.

The initial problem was that I didn't want to do multiple crosstable loads for the spreadsheet supplied to me as this would have generated thousands of line of unnecessary code.

I knew that the spreadsheet was fairly regimented in its pattern, it had a heading row with the category of KPI and then the months going along the columns and then the next four rows were departments. This pattern was repeated for each of the KPI's

What I did was :-

1: Create an inline table to hold all the KPI's (Category)

2: Do one crosstable load (using the data transformation steps to remove blank rows,etc.) and named the table RawData.

Then I just set up a couple of variable to hold the number of records in each of the tables and looped around the RawData file.

code below.

let vrawdatanoofrows=NoOfRows('RawData'); // Count the number of rows in the raw data table
let vcategorynoofrows = NoOfRows('Category'); // count the number of rows in the category table
let vchunksofdata = $(vrawdatanoofrows)/$(vcategorynoofrows); // Dataset
For vri = 0 to ($(vrawdatanoofrows)-1) // Set up the for loop
let vci = floor($(vri)/$(vchunksofdata)); // Get the category number
let vNewBranch = peek('tmpBranch',$(vri),'RawData'); //Get the value of the header field in table RawData for row vri
let vNewCategory = peek('Category',$(vci),'Category'); // Get the value of the field category in table category for row vci
let vNewMonth = date(num#(peek('KPIMonth',$(vri),'RawData'))); // Get the Value of the field KPIMOnth in the table RawData for row vri
let vNewData =  peek('KPIData',$(vri),'RawData'); // Get the value of the field KPIData in the table Raw data for row vri

NewCategory: // Create an inline table to hold the new data
Load * INLINE [
NewBranch,NewCategory,NewMonth,NewData
$(vNewBranch),$(vNewCategory),$(vNewMonth),$(vNewData)
];
 
next // Next vri
Drop table RawData;

Hope this helps anyone with a similar problem.

On the other hand if it could be done better I would welcome your comments.