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.
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
Highlighted
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