Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Giovane
Creator
Creator

What is happening with my script ?

Hello fellows...

If I use the following script I get the showed result:

Script...

TempTest:

LOAD * INLINE [

    Number, Begin, End

    1, 1, 1

    2, 1, 2

    3, 1, 3

    4, 1, 4

    5, 1, 5

];


MinMaxNumber:

LOAD

Min(Number) as MinNumber,

Max(Number) as MaxNumber

Resident

TempTest;


LET vMinNumber = Peek('MinNumber',0,'MinMaxNumber');


LET vMaxNumber = Peek('MaxNumber',0,'MinMaxNumber');


FOR I = 0 to $(vMaxNumber) - 1

LET vNumber = Peek('Number',I,'TempTest');

LET vBegin    = Peek('Begin',I,'TempTest');

LET vEnd       = Peek('End',I,'TempTest');

Test:

LOAD

$(vNumber)                            as Data,

$(vBegin) + IterNo() - 1          as Sequence 

AutoGenerate(1)

While

$(vBegin) + IterNo() - 1  <= $(vEnd);

NEXT

DROP Table MinMaxNumber;


Result...


  

DataSequence
11
21
22
31
32
33
41
42
43
44
51
52
53
54
55


But, when I change the data source, the script doesn't work properly, as seen bellow:


Script...


TempTest:

LOAD * INLINE [

    Number, Begin, End

    1, '01/01/2018', '02/01/2018'

    2, '01/01/2018', '03/01/2018'

    3, '01/01/2018', '04/01/2018'

    4, '01/01/2018', '05/01/2018'

    5, '01/01/2018', '06/01/2018'

];


MinMaxNumber:

LOAD

Min(Number) as MinNumber,

Max(Number) as MaxNumber

Resident

TempTest;


LET vMinNumber = Peek('MinNumber',0,'MinMaxNumber');


LET vMaxNumber = Peek('MaxNumber',0,'MinMaxNumber');


FOR I = 0 to $(vMaxNumber) - 1

LET vNumber = Peek('Number',I,'TempTest');

LET vBegin    = Peek('Begin',I,'TempTest');

LET vEnd       = Peek('End',I,'TempTest');

Test:

LOAD

$(vNumber)                                as Data,

$(vBegin) + IterNo() - 1              as Sequence 

AutoGenerate(1)

While

$(vBegin) + IterNo() - 1  <= $(vEnd);

NEXT

DROP Table MinMaxNumber;


Result...


  

DataSequence
10,00049554
20,00049554
30,00049554
40,00049554
5

0,00049554

Look that the main issue isn't the Sequence filed format, but the absence of the day sequence between the first and last day of the Number field.

Can someone help me, please ?

Thanks.

1 Solution

Accepted Solutions
marcus_sommer

Change it to:

TempTest:

load Number, num(date#(Begin, 'DD/MM/YYYY')) as Begin, num(date#(End, 'DD/MM/YYYY')) as End;

LOAD * INLINE [

    Number, Begin, End

    1, '01/01/2018', '02/01/2018'

    2, '01/01/2018', '03/01/2018'

    3, '01/01/2018', '04/01/2018'

    4, '01/01/2018', '05/01/2018'

    5, '01/01/2018', '06/01/2018'

];


MinMaxNumber:

LOAD

Min(Number) as MinNumber,

Max(Number) as MaxNumber

Resident

TempTest;


LET vMinNumber = Peek('MinNumber',0,'MinMaxNumber');


LET vMaxNumber = Peek('MaxNumber',0,'MinMaxNumber');


FOR I = 0 to $(vMaxNumber) - 1

LET vNumber = Peek('Number',I,'TempTest');

LET vBegin    = Peek('Begin',I,'TempTest');

LET vEnd       = Peek('End',I,'TempTest');

Test:

LOAD

$(vNumber)                                as Data,

date($(vBegin) + IterNo() - 1)   as Sequence 

AutoGenerate(1)

While

$(vBegin) + IterNo() - 1  <= $(vEnd);

NEXT

DROP Table MinMaxNumber;

to avoid that a value like '01/01/2018' is just a division-statement.

- Marcus

View solution in original post

2 Replies
marcus_sommer

Change it to:

TempTest:

load Number, num(date#(Begin, 'DD/MM/YYYY')) as Begin, num(date#(End, 'DD/MM/YYYY')) as End;

LOAD * INLINE [

    Number, Begin, End

    1, '01/01/2018', '02/01/2018'

    2, '01/01/2018', '03/01/2018'

    3, '01/01/2018', '04/01/2018'

    4, '01/01/2018', '05/01/2018'

    5, '01/01/2018', '06/01/2018'

];


MinMaxNumber:

LOAD

Min(Number) as MinNumber,

Max(Number) as MaxNumber

Resident

TempTest;


LET vMinNumber = Peek('MinNumber',0,'MinMaxNumber');


LET vMaxNumber = Peek('MaxNumber',0,'MinMaxNumber');


FOR I = 0 to $(vMaxNumber) - 1

LET vNumber = Peek('Number',I,'TempTest');

LET vBegin    = Peek('Begin',I,'TempTest');

LET vEnd       = Peek('End',I,'TempTest');

Test:

LOAD

$(vNumber)                                as Data,

date($(vBegin) + IterNo() - 1)   as Sequence 

AutoGenerate(1)

While

$(vBegin) + IterNo() - 1  <= $(vEnd);

NEXT

DROP Table MinMaxNumber;

to avoid that a value like '01/01/2018' is just a division-statement.

- Marcus

Giovane
Creator
Creator
Author

Hello, Marcus...

Yes, you solved my issue. Now the script does what is expected.

Thanks a lot.