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

Use For Loop to process through every row of a table

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

1 Solution

Accepted Solutions
Not applicable

Re: Use For Loop to process through every row of a table

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;

5 Replies
Not applicable

Re: Use For Loop to process through every row of a table

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

Not applicable

Re: Use For Loop to process through every row of a table

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;

Not applicable

Re: Use For Loop to process through every row of a table

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:

vridvlDate
10.00049975
20.001249375
30.001998002
40.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

Not applicable

Re: Use For Loop to process through every row of a table

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.

Not applicable

Re: Use For Loop to process through every row of a table

Thanks Nigel.

Community Browser