topic Variables with load statement in QlikView

Variables with load statement

Mon, 14 Jun 2010 16:20:23 GMT

Hi,

I'm exporting information from Project into QlikView as xml however within QlikView I want to be able to drill down the tasks and sub tasks of the project document. I therefore need to assign parent task names to each sub task. Is there any way of using variables within the table load statement? I'm importing the following script:

Regions:
LOAD ID,
Task_Name,
Duration,
Start_Date,
Finish_Date,
Percent_Comp,
Cost,
Scheduled_Work,
Outline_Level
FROM

Basically want to create a new field called Region and if the Outline_Level equals 1 then use this value for the region, if not use the value in the current variable? So something like if([Outline_Level=1],Task_Name,Variable) as Region. However I don't know if you can use variables within the actual script? SET and LET don't work.

Any help would be appreciated

Cheers

Variables with load statement

fernandotoledo — Mon, 14 Jun 2010 21:21:58 GMT

It will work fine but you must call the variable with

$(VARIABLE)

best regards,

Fernando

Variables with load statement

Tue, 15 Jun 2010 10:34:57 GMT

Thanks for your reply again Fernando.

I've used variables and knew about the $, however I'm not sure if you can actually set (or let) variables inside a load statement. I've used the following code at the moment, however this loads each line individually, which currently at 3,800 isn't a huge wait. However is there a more efficient way of cleasning the data during the actual load statement?

The code is basically looking at creating 3 additional fields where 3 levels of hierachy from MS project will be saved, this can then be used to drill-down within QlikView. When the Outline level 1 is excountered the variable is changed to the value and is used for all records until an outline level 1 is again encountered (same for levels 2 and 3).

Load_Data:
LOAD RecNo() as Record_Number,
Task_Name,
Duration,
Start_Date,
Finish_Date,
Percent_Comp,
Cost,
Scheduled_Work,
Outline_Level
FROM Table

Max_Records:
load max(Record_Number) as Record_Count
resident Load_Data;

let vRecord_Count = peek('Record_Count',0,'Max_Records');
let a = 1;

do while a<=vRecord_Count

Base_Data:
Load *,
if([Outline_Level]=1,[Task_Name],'$(vLevel1)') as Level1,
if([Outline_Level]=2,[Task_Name],'$(vLevel2)') as Level2,
if([Outline_Level]=3,[Task_Name],'$(vLevel3)') as Level3
resident Load_Data
Where Record_Number=$(a);

let vOutLineLevel= (lookup('Outline_Level','Record_Number',$(a)));
let vTaskName= (lookup('Task_Name','Record_Number',$(a)));

let vLevel1= if('$(vOutLineLevel)'=1,'$(vTaskName)','$(vLevel1)');
let vLevel2= if('$(vOutLineLevel)'=2,'$(vTaskName)','$(vLevel2)');
let vLevel3= if('$(vOutLineLevel)'=3,'$(vTaskName)','$(vLevel3)');

let a=a+1;

loop

DROP TABLE Load_Data, Max_Records;

Cheers

Variables with load statement

tanelry — Tue, 15 Jun 2010 15:39:21 GMT

Try if that works:

Load_Data:
LOAD RecNo() as Record_Number,
Task_Name,
Outline_Level,
...etc...
FROM Table;

left join load distinct
Record_Number,
if(Outline_Level=1,Task_Name,peek(Level1)) as Level1
resident Load_Data;

left join load distinct
Record_Number,
if(Outline_Level=2,Task_Name,if(Outline_Level>2,peek(Level2))) as Level2
resident Load_Data;

left join load distinct
Record_Number,
if(Outline_Level=3,Task_Name,if(Outline_Level>3,peek(Level3))) as Level3
resident Load_Data;

Variables with load statement

Tue, 15 Jun 2010 16:12:56 GMT

Thanks, much simpler!!