# QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Contributor II

## Calculated Sequence through dimensions

Hello All,

I need some help with scripting logic.

So, I have data set with four columns UniqueID, OriginalDate, Daily Sequence, Line.  For the sake of simplicity,we can consider the following data:

So, Each Unique ID is associated with OriginalDate, Line and DailySequence. I have 2 lines namely G and H. G line has a capacity of 8  and H line has a capacity of 4 . However,right now, G and H lines have only 5 uniqueid per date. I would like to fill that upto capacity and modify the original date. so,I need 2 extra columns namely TotalSequence and ActualDate

TotalSequence: This is basically incremental sequence for each line. For example, G line has a capacity of 8  but only 5 uniqueid exists for a given date and i would like to increment the sequence to 8 and start a fresh sequence after 8.

ActualDate: Once i have the totalSequence, i would like to calculate the date based on the capacity. So the first 8 jobs will the get the earliest date and increase them accordingly filling up the capacity.

A sample calculation of the need is put in the table.

I have added desired columns in red in the table so my need is clearly understood. I have also attached qlik file with fulldata.

• Set vGCapacity=8;
• Set vLCapacity=4;
 OriginalDate UniqueID Daily Sequence Line TotalSequence ActualDate 1/13/2020 11315346 1 G 1 2020-01-13 1/13/2020 19579152 2 G 2 2020-01-13 1/13/2020 13366807 3 G 3 2020-01-13 1/13/2020 17663764 4 G 4 2020-01-13 1/13/2020 18913092 5 G 5 2020-01-13 1/14/2020 17906721 1 G 6 2020-01-13 1/14/2020 18355589 2 G 7 2020-01-13 1/14/2020 11032136 3 G 8 2020-01-13 1/14/2020 10065524 4 G 9 2020-01-14 1/14/2020 12081434 5 G 10 2020-01-14 1/15/2020 14616012 1 G 11 2020-01-14 1/15/2020 13723431 2 G 12 2020-01-14 1/15/2020 12158813 3 G 13 2020-01-14 1/15/2020 14026715 4 G 14 2020-01-14 1/15/2020 15530239 5 G 15 2020-01-14 1/13/2020 18624928 1 H 1 2020-01-13 1/13/2020 10836493 2 H 2 2020-01-13 1/13/2020 17847877 3 H 3 2020-01-13 1/14/2020 16668400 1 H 4 2020-01-13 1/14/2020 18597622 2 H 5 2020-01-14 1/14/2020 14983917 3 H 6 2020-01-14 1/15/2020 17165949 1 H 7 2020-01-14 1/15/2020 19436434 2 H 8 2020-01-14 1/15/2020 12340878 3 H 9 2020-01-15
Labels (1)
• ### Calculate Sequence

1 Solution

Accepted Solutions
Highlighted
MVP

## Re: Calculated Sequence through dimensions

may be this

``````T1:
OriginalDate,
UniqueID,
"Daily Sequence",
"Line"
FROM [lib://Qlik]
(html, utf8, embedded labels, table is @1);

T2:
AutoNumber(RowNo(),"Line") as TotalSequence,
if( "Line"='G',ceil(AutoNumber(RowNo(),"Line")/8),ceil(AutoNumber(RowNo(),"Line")/4)) as SequencBreakup
Resident T1
Order By "Line",OriginalDate;

Drop Table T1;

T3:
date(if("Line"<>Previous("Line") or RowNo()=1,OriginalDate,
if(SequencBreakup<>Previous(SequencBreakup),Peek(ActualDate)+1,Peek(ActualDate)))) as ActualDate
Resident T2;

Drop Table T2;``````
2 Replies
Highlighted
MVP

## Re: Calculated Sequence through dimensions

may be this

``````T1:
OriginalDate,
UniqueID,
"Daily Sequence",
"Line"
FROM [lib://Qlik]
(html, utf8, embedded labels, table is @1);

T2:
AutoNumber(RowNo(),"Line") as TotalSequence,
if( "Line"='G',ceil(AutoNumber(RowNo(),"Line")/8),ceil(AutoNumber(RowNo(),"Line")/4)) as SequencBreakup
Resident T1
Order By "Line",OriginalDate;

Drop Table T1;

T3:
date(if("Line"<>Previous("Line") or RowNo()=1,OriginalDate,
if(SequencBreakup<>Previous(SequencBreakup),Peek(ActualDate)+1,Peek(ActualDate)))) as ActualDate
Resident T2;

Drop Table T2;``````
Highlighted
Valued Contributor II

## Re: Calculated Sequence through dimensions

Maybe something like the below

Final:
load * , if(TotalSequence=1 , originalDate ,IF( findoneof(Peek(Div),'.') , Peek(ActualDate),DATE(date(date#(Peek(ActualDate),'MM/DD/YYYY'),'YYYY-MM-DD')+1))) AS ActualDate;

load * ,if( Line='G' , TotalSequence/8 , TotalSequence/4) as Div ;

LOAD *, if( Line<>Peek(Line) ,1, Peek(TotalSequence)+1) as TotalSequence INLINE [

originalDate, UniqueID, Daily Sequence, Line
1/13/2020, 11315346, 1, G
1/13/2020, 19579152, 2, G
1/13/2020, 13366807, 3, G
1/13/2020, 17663764, 4, G
1/13/2020, 18913092, 5, G
1/14/2020, 17906721, 1, G
1/14/2020, 18355589, 2, G
1/14/2020, 11032136, 3, G
1/14/2020, 10065524, 4, G
1/14/2020, 12081434, 5, G
1/15/2020, 14616012, 1, G
1/15/2020, 13723431, 2, G
1/15/2020, 12158813, 3, G
1/15/2020, 14026715, 4, G
1/15/2020, 15530239, 5, G
1/13/2020, 18624928, 1, H
1/13/2020, 10836493, 2, H
1/13/2020, 17847877, 3, H
1/14/2020, 16668400, 1, H
1/14/2020, 18597622, 2, H
1/14/2020, 14983917, 3, H
1/15/2020, 17165949, 1, H
1/15/2020, 19436434, 2, H
1/15/2020, 12340878, 3, H
];