What is happening with my script ?

[Giovane]

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

Data	Sequence
1	1
2	1
2	2
3	1
3	2
3	3
4	1
4	2
4	3
4	4
5	1
5	2
5	3
5	4
5	5

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

Data	Sequence
1	0,00049554
2	0,00049554
3	0,00049554
4	0,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.

[marcus_sommer · Accepted solution]

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]

Hello, Marcus...

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

Thanks a lot.