Loop through multiple fields to get Peek Value

[#1]

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

[anbu1984]

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

[#3]

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

Regards

Rahul

[#4]

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

[maxgro]

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

[#6]

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

[maxgro]

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;

[#8]

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.

[maxgro · Accepted solution]

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;

[#10]

That did the trick, thanks much for your help.

Regards

Rahul