Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Loop through multiple fields to get Peek Value

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

1 Solution

Accepted Solutions
MVP
MVP

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

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;



1.png

9 Replies
anbu1984
Honored Contributor III

Re: Loop through multiple fields to get Peek Value

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

Not applicable

Re: Loop through multiple fields to get Peek Value

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

Regards

Rahul

Not applicable

Re: Loop through multiple fields to get Peek Value

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

NEXT

Please change the vcount according to the field count.

Thanks,

Prabhu

MVP
MVP

Re: Loop through multiple fields to get Peek Value

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

Not applicable

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

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

MVP
MVP

Re: Loop through multiple fields to get Peek Value

with real data, see attachment

just some minor change to Prabhu script, thanks


RESULT

1.png


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;

Not applicable

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

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.

MVP
MVP

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

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;



1.png

Not applicable

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

That did the trick, thanks much for your help.

Regards

Rahul

Community Browser