topic Re: Is it possible to set a field into a variable in the load script in QlikView

Is it possible to set a field into a variable in the load script

helen_pip — Fri, 19 Aug 2016 08:58:12 GMT

Dear Qlikview user

Is it possible to set the field of another table into a variable in the load script?

If so, could someone kindly show me the correct code

Kind Regards

Helen

Re: Is it possible to set a field into a variable in the load script

vinieme12 — Fri, 19 Aug 2016 09:00:29 GMT

Can you post sample data and variable output required??

It's easier to visualize what you want to achieve

Re: Is it possible to set a field into a variable in the load script

helen_pip — Fri, 19 Aug 2016 09:05:14 GMT

Hello Vineeth

I have a filed called Division and the values within the Division are

Division A

Division B

Division C

Division D

I created a variable in the variable overview and as the field had already been loaded it was easy to create the variable

I.e. v_Div = Division

I then use the variables as a dimension in a drill down table

However for other purposes I now need to set the variable in the load script where the variable is holding the field name exactly as before, but as the variable and field name are both being loaded into the script, I am unsure how to set the variable in the script

Kind Regards

Helen

Re: Is it possible to set a field into a variable in the load script

vinieme12 — Fri, 19 Aug 2016 09:23:37 GMT

Try below

variable Output = Division A;Division B;Division C;Division D

FACT:

LOAD * INLINE [

Division,Value

Division A,1

Division B,2

Division C,3

Division D,4

];

NoConcatenate

DivisionsList:

LOAD Distinct Division as DivisionIS

RESIDENT FACT;

let DivisionCount = NoOfRows('DivisionsList')-1;

LET varDivision = '';

For i = 0 to $(DivisionCount)

LET varDivision = varDivision & if($(i)>=1,chr(59)) & Peek('DivisionIS',$(i),'DivisionsList');

Next i

Re: Is it possible to set a field into a variable in the load script

helen_pip — Fri, 19 Aug 2016 09:29:03 GMT

Hello Vineeth

Thank you for your response, but before I try, because Division is loaded in as an inline, it is not actually referencing the actual field held in a different table

There will be other fields that I need to apply the same logic too, some of the fields hold hundreds of values, so would an inline still work in those instances?

Kind Regards

Helen

Re: Is it possible to set a field into a variable in the load script

vinieme12 — Fri, 19 Aug 2016 09:31:50 GMT

I've just loaded inline to duplicate the scenario of your fact table

You should Load your FACT tables as usual , what you really need is the table with distinct division values

DivisionsList:

LOAD Distinct Division as DivisionIS

RESIDENT FACT;

let DivisionCount = NoOfRows('DivisionsList')-1;

LET varDivision = '';

For i = 0 to $(DivisionCount)

LET varDivision = varDivision & if($(i)>=1,chr(59)) & Peek('DivisionIS',$(i),'DivisionsList');

Next i

Steps

LOAD FACT
Load a Table to hold distinct values, that you need to store in variable
loop over the distinct values and add to variable

Re: Is it possible to set a field into a variable in the load script

helen_pip — Fri, 19 Aug 2016 09:33:14 GMT

Thank you

I shall give it a go and let you know how I get on

Thank you for your efforts

Kind Regards

Helen

Re: Is it possible to set a field into a variable in the load script

helen_pip — Fri, 19 Aug 2016 11:09:57 GMT

Hello Vineeth

Your example certainly worked with the Division added in from the fact table

I have now tried adding in another field in the same fashion, but now the script is erroring, would it be o.k. to take a look, and let me know where I can correct it, and then I know exactly how the process works when I need to add more variables

Temp:

LOAD

Division,

Care_Group

resident

main_data;

NoConcatenate

TempList:

LOAD Distinct Division as DivisionIS,

Care_Group as Care_GroupIS

Resident main_data;

let DivisionCount = NoOfRows('TempList')-1;

let Care_GroupCount = NoOfRows('Templist')-1;

LET vField_OPD1_Table = '';

LET vField_OPD2_Table = '';

For i = 0 to $(DivisionCount)

For i = 0 to $(Care_GroupCount)

LET vField_OPD1_Table = varDivision & if($(i)>=1,chr(59)) & Peek('DivisionIS',$(i),'TempList');

LET vField_OPD2_Table = varCare_Group & if($(i)>=1,chr(59)) & Peek('Care_GroupIS',$(i),'TempList');

Next i

Re: Is it possible to set a field into a variable in the load script

vinieme12 — Fri, 19 Aug 2016 12:11:18 GMT

Try like this

Temp:

LOAD

Division,

Care_Group

resident

main_data;

NoConcatenate

TempList:

LOAD

concat(Distinct Division,',') as DivisionIS,

concat(Distinct Care_Group,',') as Care_GroupIS

Resident Temp;

Let varDivision = peek('DivisionIS',0,'TempList');

Let varCareGroup = peek('Care_GroupIS',0,'TempList');

Drop table TempList;