Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

eruditio
Contributor III

Create Fields using IterNo()

Hi,

I am trying to generate the hours of the day within the load script.

LET b = Chr(91)&'Hour '&Iterno()&Chr(93)

LOAD

( - 1 + IterNo()) as $(b)

While IterNo() <24;

LOAD *

Resident Table;

It is not giving me one record with 24 fields, but 24 records.

Can someone advise how to rectify this situation?

Thanks,

Antoine

1 Solution

Accepted Solutions
pgrenier
Contributor III

Re: Create Fields using IterNo()

Hello Antoine,

I am not quite sure if I understood your need exactly, but would this code snippet help?

Table:

LOAD * inline [

value, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23

hello world, 5, 4, 3, 2, 1, 5, 4, 3, 2, 1, 4, 3, 2, 1, 5, 6, 7, 8, 9, 4, 3, 2, 1, 1, 9

];

LET b=Null();

FOR i=0 to 23

  LET b = '$(b)' & If($(i)=0, '', ',') &'[$(i)] as [Hour $(i)]';

NEXT i

LOAD value, $(b);

LOAD *

Resident Table;

DROP Table Table;

You shall find attached a QVW file with the script, regards,

Philippe

4 Replies
fernando_tonial
Valued Contributor

Re: Create Fields using IterNo()

Hi, you can try this:

LOAD

(IterNo() - 1) as Hour

Autogenerate 1

While IterNo() <=24;

Best Regards.

Tonial.

pgrenier
Contributor III

Re: Create Fields using IterNo()

Hello Antoine,

I am not quite sure if I understood your need exactly, but would this code snippet help?

Table:

LOAD * inline [

value, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23

hello world, 5, 4, 3, 2, 1, 5, 4, 3, 2, 1, 4, 3, 2, 1, 5, 6, 7, 8, 9, 4, 3, 2, 1, 1, 9

];

LET b=Null();

FOR i=0 to 23

  LET b = '$(b)' & If($(i)=0, '', ',') &'[$(i)] as [Hour $(i)]';

NEXT i

LOAD value, $(b);

LOAD *

Resident Table;

DROP Table Table;

You shall find attached a QVW file with the script, regards,

Philippe

eruditio
Contributor III

Re: Create Fields using IterNo()

Thank you Philippe, this solved it!

Kind regards,


Antoine

EDIT:

I also managed  to reduce the number of steps:

LET b=Null();


FOR i=0 to 23
   LET b = '$(b)' & If($(i)=0, '', ',') &'$(i) as [Hour $(i)]';
NEXT i

FOR EACH b

[Table 2]:
LOAD
*,
$(b)

Resident [A previously Loaded Table 1]

eruditio
Contributor III

Re: Create Fields using IterNo()

Thank you Tonial for your answer, this is not what I am looking for.

I was trying to create one record with 24 fields to represent the hours of the day.

Community Browser