topic Re: Create Fields using IterNo() in QlikView

Create Fields using IterNo()

Anonymous — Mon, 16 Sep 2013 16:02:00 GMT

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

Re: Create Fields using IterNo()

fernando_tonial — Tue, 17 Sep 2013 00:07:56 GMT

Hi, you can try this:

LOAD 
(IterNo() - 1) as Hour
Autogenerate 1
While IterNo() <=24;

Best Regards.

Tonial.

Re: Create Fields using IterNo()

pgrenier — Tue, 17 Sep 2013 00:31:16 GMT

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

Re: Create Fields using IterNo()

Anonymous — Tue, 17 Sep 2013 07:47:38 GMT

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]

Re: Create Fields using IterNo()

Anonymous — Tue, 17 Sep 2013 07:48:34 GMT

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.