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

[helen_pip]

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

[vinieme12]

Can you post sample data and variable output required??

It's easier to visualize what you want to achieve

[helen_pip]

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

[vinieme12]

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

[helen_pip]

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

[vinieme12]

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

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

[helen_pip]

Thank you

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

Thank you for your efforts

Kind Regards

Helen

[helen_pip]

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

[vinieme12]

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;