topic How to pass all field values into variables. in QlikView

How to pass all field values into variables.

Fri, 14 Aug 2015 19:16:47 GMT

Hi,

I am trying to pass all field values into variables but after reload i am able to see only one value.

DataToReduce:

LOAD Item AS Item1,

Status AS Status1,

Store As Store1

FROM

Item.xlsx

(ooxml, embedded labels, table is DataToReduce);

let VNo.ofFields = NoOfFields('DataToReduce'); //To find No.of Fields in the table

for i=0 to $(VNo.ofFields)-1 //

let VFieldName=FieldName($(i),'DataToReduce'); //To find FieldName in the ith position.

//let VNo.ofFieldValues=Count(Distinct $(VFieldName)); //To count noof distinct values of the field.

let VNo.ofFieldValues=FieldValueCount('$(VFieldName)');

for j=0 to $(VNo.ofFieldValues)-1 //

// let VFieldvalue=FieldValue('$(VFieldName)',$(j));

let VFieldvalue=peek('$(VFieldName)',$(j),'DataToReduce'); //To pass field value in jth position.

next j; //increment field value.

next i; //increment the fields.

Can any one help on this issue.

Thanks

Anil

Re: How to pass all field values into variables.

swuehl — Fri, 14 Aug 2015 19:22:26 GMT

You overwrite the previous value in each iteration.

Not sure what you are trying to achieve. You can try

let VFieldvalue= peek('$(VFieldName)',$(j),'DataToReduce') & ', ' & VFieldvalue; //To pass field value in jth position.

or use CONCAT aggregation in a table LOAD to aggregate all values and then a single peek() to get the string into a variable.

Re: How to pass all field values into variables.

Fri, 14 Aug 2015 19:34:10 GMT

My requirement is i have to load data from excel.

In that no.of field and Field value may vary each time .

And These field values have to select the data already loaded from the qvd.

After post reload the data comes from excel that matchs with qvd should be excluded from qlikview application.

Main problem we don't know what are fields and there values will going to be in excel.

Re: How to pass all field values into variables.

swuehl — Fri, 14 Aug 2015 19:46:12 GMT

Not sure if I understand what you are trying to achieve. Could you elaborate with some sample lines of data and your requested result?

My first thought that you might want to look into a WHERE clause with EXISTS() / NOT EXISTS() function instead of using variables.

Re: How to pass all field values into variables.

Fri, 14 Aug 2015 19:59:00 GMT

Reduction field data is loaded from excel file .

You can see "DatatoReduce" sheet in the attached excel file.

In this file ,fields may vary .But after Post reload the data has to reduce which is already loaded from Data Sheet.

But in real No.of field and field values may vary.

Data has to reduce after post reload.So, we are not using where not exists in the script.

Kindky check the above attachments;

Re: How to pass all field values into variables.

Fri, 14 Aug 2015 20:01:03 GMT

In that case i want read each field and there values dynamically.

Onpost reload data have to reduce.