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: 
poluvidyasagar
Creator II
Creator 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;
OriginalDateUniqueIDDaily SequenceLineTotalSequenceActualDate
1/13/2020113153461G12020-01-13
1/13/2020195791522G22020-01-13
1/13/2020133668073G32020-01-13
1/13/2020176637644G42020-01-13
1/13/2020189130925G52020-01-13
1/14/2020179067211G62020-01-13
1/14/2020183555892G72020-01-13
1/14/2020110321363G82020-01-13
1/14/2020100655244G92020-01-14
1/14/2020120814345G102020-01-14
1/15/2020146160121G112020-01-14
1/15/2020137234312G122020-01-14
1/15/2020121588133G132020-01-14
1/15/2020140267154G142020-01-14
1/15/2020155302395G152020-01-14
1/13/2020186249281H12020-01-13
1/13/2020108364932H22020-01-13
1/13/2020178478773H32020-01-13
1/14/2020166684001H42020-01-13
1/14/2020185976222H52020-01-14
1/14/2020149839173H62020-01-14
1/15/2020171659491H72020-01-14
1/15/2020194364342H82020-01-14
1/15/2020123408783H92020-01-15
Labels (1)
1 Solution

Accepted Solutions
Kushal_Chawda

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;

View solution in original post

2 Replies
Kushal_Chawda

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
Specialist II
Specialist II

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