Calculated Sequence through dimensions

[poluvidyasagar]

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

[Kushal_Chawda · Accepted solution]

may be this

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

T2:
Load *,
     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:
Load *,
     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;

[qliksus]

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
];