topic Re: Loop through multiple fields to get Peek Value in QlikView

Loop through multiple fields to get Peek Value

Fri, 18 Apr 2014 08:23:03 GMT

Hi,

I have a table with multiple variables that are set at different times. Please see excel attached. What I want to be able to do is capture that last value of the variable for all subsequent time. For example: Field 1 was set to 'true' at 2013-12-16 06:58:57 for all 17 entries after that I want Field 1 to have 'true' as the value and for Field 2 which was set at 2013-12-16 06:59:58 all previous records would be blank and all blank records should take the peek value. I could use previous however it would mean having to rewrite the statement many times as I have close to 100 variables. Could someone please suggest a solution? Regards Rahul

Re: Loop through multiple fields to get Peek Value

anbu1984 — Fri, 18 Apr 2014 09:40:29 GMT

Load Key,Sale,If(IsNull(Field1),Peek(Field1),Field1) As Field1,If(IsNull(Field2),Peek(Field2),Field2) As Field2;

LOAD Key,

Sale,

VarValue_Time,

Field1,

Field2,

Field3,

Field4,

Field5,

Field6,

Field7,

Field8,

Field9,

Field10,

Field11,

Field12,

Field13,

Field14,

Field15,

Field16,

Field17,

Field18,

Field19,

Field20,

Field21

FROM

SampleData.xls

Re: Loop through multiple fields to get Peek Value

Fri, 18 Apr 2014 10:08:19 GMT

Thanks Anbu, however as I said I have more than 100 fields. I believe I may need a for loop

Regards

Rahul

Re: Loop through multiple fields to get Peek Value

Fri, 18 Apr 2014 11:45:46 GMT

Hi Rahul,

Use this..

Let vcount =21;

FOR i=1 to $(vcount)

Let vname = 'Field'&i;

if(i=1) then

Test:

Load VarValue_Time,Key,Sale,If(IsNull($(vname)),Peek($(vname)),$(vname)) As $(vname);

LOAD Key,

Sale,

VarValue_Time,

$(vname)

FROM

(biff, embedded labels, table is Sheet1$);

ELSE

join(Test)

Load VarValue_Time,Key,Sale,If(IsNull($(vname)),Peek($(vname)),$(vname)) As $(vname);

LOAD Key,

Sale,

VarValue_Time,

$(vname)

FROM

(biff, embedded labels, table is Sheet1$);

ENDIF

Please change the vcount according to the field count.

Thanks,

Prabhu

Re: Loop through multiple fields to get Peek Value

maxgro — Fri, 18 Apr 2014 12:13:20 GMT

did you try to start with a crosstable load to transform columns (field1....field100) in rows?

Re: Re: Loop through multiple fields to get Peek Value

Fri, 18 Apr 2014 12:35:28 GMT

Dear Prabhu,

Thanks but that resulted in the same table. Please see test file attached. Also the field name in the sample I posted were dummies in order to give you correct info I an loaded some data in the test file.

Regards

Rahul

Re: Loop through multiple fields to get Peek Value

maxgro — Sat, 19 Apr 2014 06:27:51 GMT

with real data, see attachment

just some minor change to Prabhu script, thanks

RESULT

SCRIPT

Let vcount =NoOfFields('Data2')-3;

trace $(vcount);

FOR i=4 to 3+$(vcount)

Let vname = FieldName(i ,'Data2'); //'Field'&i;

trace $(vname);

if (i=4) then

Test2:

LOAD Key, Sale, VarValue_Time,

If(len(trim($(vname)))=0, Peek($(vname)), $(vname)) As $(vname)

Resident Data2

Order by Key, Sale, VarValue_Time;

ELSE

join(Test2)

LOAD Key, Sale, VarValue_Time,

If(len(trim($(vname)))=0, Peek($(vname)), $(vname)) As $(vname)

Resident Data2

Order by Key, Sale, VarValue_Time;

ENDIF

NEXT;

Re: Re: Loop through multiple fields to get Peek Value

Mon, 21 Apr 2014 08:29:01 GMT

Dear Massimo,

Thanks a bunch, that did work for most part. However I have some field in my table that are leading to a syntax error and are called

'PT-EligibleForChat'

'TIS-30',

'TIS-60',

'TIS-120',

'TIS-90',

'Hot-lead' and

'Prehot-Lead'

sign in the field name that is causing the problem. Can you think of any way to incorporate the above pattern into the script and also let me know what you believe the '-' is causing the problem it the script.

Please see the error message I got attached.

Thanks and Regards

Rahul.

Re: Re: Re: Loop through multiple fields to get Peek Value

maxgro — Mon, 21 Apr 2014 08:59:14 GMT

I try with your real data changing campaign_medium to campaign-medium

I just add " to the beginning and end of field name (bold)

Let vcount =NoOfFields('Data2')-3;

trace $(vcount);

FOR i=4 to 3+$(vcount)

Let vname = '"' & FieldName(i ,'Data2') & '"'; // it was FieldName(i ,'Data2')

trace $(vname);

if (i=4) then

Test2:

LOAD Key, Sale, VarValue_Time,

If(len(trim($(vname)))=0, Peek($(vname)), $(vname)) As $(vname)

Resident Data2

Order by Key, Sale, VarValue_Time;

ELSE

join(Test2)

LOAD Key, Sale, VarValue_Time,

If(len(trim($(vname)))=0, Peek($(vname)), $(vname)) As $(vname)

Resident Data2

Order by Key, Sale, VarValue_Time;

ENDIF

NEXT;

Re: Re: Re: Loop through multiple fields to get Peek Value

Mon, 21 Apr 2014 09:08:08 GMT

That did the trick, thanks much for your help.

Regards

Rahul