Use For Loop to process through every row of a table

[#1]

Hi,

I am trying to use for loop to go through every single row of a table and do some operations. However, I can not manage to read each row successfully. Can someone have a look at the attached QVW and tell me why table "TestReplicate" can only replicate one row of the original table "IncidentTemp"? Many thanks in advance.

Regards,

Yang

[#2]

Hi

Change this code:

TestReplicate:

LOAD * INLINE [
vrid,     vIDate
$(vRecordID), $(vIncidentDate)
];

To This

LOAD $(vRecordID) AS vrid

,$(vIncidentDate AS vlDate

Autogenerate(1);

That should do the trick

[#3 · Accepted solution]

Sorry, went a bit quick then, but this works, notice the different use of LET & SET, in this case you need to use SET as you don't want the expression to be immediately evaluated at that time,

IncidentTemp:

LOAD * INLINE [
    RecordID, IncidentDate, Value
    1, 01/01/2001, 100
    2, 05/02/2001, 154
    3, 12/03/2002, 200
    4, 09/08/2004, 53
];

Let vNoOfRows = NoOfRows('IncidentTemp');

FOR i=0 to $(vNoOfRows)-1
  SET vRecordID=Peek('RecordID',$(i),'IncidentTemp');
  SET vIncidentDate=Peek('IncidentDate',$(i),'IncidentTemp');

TestReplicate:
LOAD IF(ISNULL($(vRecordID)),'NULL'&$(i),$(vRecordID)) as vrid
, IF(ISNULL($(vIncidentDate)),'NULL'&$(i),$(vIncidentDate)) as vlDate AutoGenerate(1);

NEXT i;

[#4]

Hi Nigel,

Thank you very much. It works perfectly.

Just out of my curiosity, when I used "let" instead of "set", the data that I will get in TestReplicate table will be:

vrid	vlDate
1	0.00049975
2	0.001249375
3	0.001998002
4	0.000561377

Where are those numbers for "vIDate" coming from? They are not timestamps of original "IncidentDate". In addition, it seems "let" works well for ID column and Value column. Only for "IncidentDate" column, it needs to be changed to "set". Do you know why?

Thanks.

Regards,

Yang

[#5]

I'm not sure why it would be different for SET & LET in the above case, it certainly shouldn't be.

Having said that, QlikView does do very odd things with dates, you will find that by far the best way to avoid date issues is to convert them to numbers in the load script and then use date functions in your charts to show what you want. This always makes things easier if you want to create expressions and/or dimensions based around dates.

So, for you date, in the load script you should propbably do something like vlDate = NUM(IncidentDate).

Then, in your charts, you should convert them back to date formats using something like Date(vlDate,'DD/MM/YYYY')

Nigel.

[#6]

Thanks Nigel.