Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
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;

View solution in original post

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