Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Start date and end date in two different rows

Hey Qlikview community,

I'm working on a dashboard project with the capability of analyzing customer alerts over time, so I'll need the end dates and start dates of alerts. Unfortunately, the way this stored in the DB is unusual. I see the logic of how to do it, but can't quite translate it into a Qlikview script.

Here's the information I have. A company can only have one alert active at a time. An alert's beginning date is the time stamp where before = false and after = true. The end date is the time stamp where before = true and after = false.

whatIHave.png

Sorting by company, and then by time stamp, it would be simple to manually copy and paste the time stamps to a new column "Begin" and a new column "End," creating records like below:

whatIWant.png

The real data has hundreds of companies' alerts, not just Lulz Corp. Any ideas on how this might be accomplished with a Qlikview script? Thanks!

1 Solution

Accepted Solutions

Re: Re: Start date and end date in two different rows

Hi,

maybe like this:

QlikCommunity_Thread_142617_Pic1.JPG.jpg

tabInput:

LOAD * Inline [

    Company_Id, Company_Name, Field_Label, Time_Stamp, Before_Value, After_Value

    1, Lulz Corp., At Risk Alert, 7/26/2011, FALSE, TRUE

    1, Lulz Corp., At Risk Alert, 10/1/2011, TRUE, FALSE

    1, Lulz Corp., At Risk Alert, 10/7/2011, FALSE, TRUE

    1, Lulz Corp., At Risk Alert, 1/13/2013, TRUE, FALSE

    1, Lulz Corp., At Risk Alert, 2/12/2013, FALSE, TRUE

    1, Lulz Corp., At Risk Alert, 2/17/2013, TRUE, FALSE

    1, Lulz Corp., At Risk Alert, 4/12/2013, FALSE, TRUE

    1, Lulz Corp., At Risk Alert, 6/24/2013, TRUE, FALSE

    1, Lulz Corp., At Risk Alert, 7/3/2013, FALSE, TRUE

    1, Lulz Corp., At Risk Alert, 7/29/2014, TRUE, FALSE

    1, Lulz Corp., At Risk Alert, 8/18/2014, FALSE, TRUE

    1, Lulz Corp., At Risk Alert, 8/30/2014, TRUE, FALSE

    2, ACME Inc., At Risk Alert, 8/26/2011, FALSE, TRUE

    2, ACME Inc., At Risk Alert, 11/1/2011, TRUE, FALSE

    2, ACME Inc., At Risk Alert, 11/7/2011, FALSE, TRUE

    2, ACME Inc., At Risk Alert, 2/13/2013, TRUE, FALSE

    2, ACME Inc., At Risk Alert, 3/12/2013, FALSE, TRUE

    2, ACME Inc., At Risk Alert, 3/17/2013, TRUE, FALSE

    2, ACME Inc., At Risk Alert, 5/12/2013, FALSE, TRUE

    2, ACME Inc., At Risk Alert, 7/24/2013, TRUE, FALSE

    2, ACME Inc., At Risk Alert, 8/3/2013, FALSE, TRUE

    2, ACME Inc., At Risk Alert, 8/29/2014, TRUE, FALSE

    2, ACME Inc., At Risk Alert, 9/18/2014, FALSE, TRUE

    2, ACME Inc., At Risk Alert, 9/30/2014, TRUE, FALSE

];

tabTemp:

LOAD RangeSum(Peek(ID),-(not Before and After)) as ID,

    Company_Id,

    Company_Name,

    Field_Label,

    If(not Before and After, 'Begin', If(Before and not After, 'End')) as Time_Stamp_Type,

    Time_Stamp;  

LOAD *,

    Evaluate(Before_Value&'()') as Before,

    Evaluate(After_Value&'()') as After

Resident tabInput

Order By Company_Id, Company_Name, Field_Label, Time_Stamp;

tabOutput:

Generic LOAD * Resident tabTemp;

DROP Table tabInput, tabTemp;

hope this helps

regards

Marco

7 Replies

Re: Start date and end date in two different rows

a generic load with some conditions should work

morgankejerhag
Contributor II

Re: Start date and end date in two different rows

Not sure if generic load will work, but otherwise you could do something like this. See attached file as well.

Data:

Load

  Company_Id,

  Company_Name,

  Field_Label,

  date#(Time_Stamp,'MM/DD/YYYY') as Time_Stamp,

  Before_Value,

  After_Value

INLINE [

  Company_Id, Company_Name, Field_Label, Time_Stamp, Before_Value, After_Value

  1, Lulz Corp., At Risk Alert, 7/26/2011, FALSE, TRUE

  1, Lulz Corp., At Risk Alert, 10/1/2011, TRUE, FALSE

  1, Lulz Corp., At Risk Alert, 10/7/2011, FALSE, TRUE

  1, Lulz Corp., At Risk Alert, 1/13/2013, TRUE, FALSE

  1, Lulz Corp., At Risk Alert, 2/12/2013, FALSE, TRUE

  1, Lulz Corp., At Risk Alert, 2/17/2013, TRUE, FALSE

  2, Second Corp., At Risk Alert, 7/26/2011, FALSE, TRUE

  2, Second Corp., At Risk Alert, 10/1/2011, TRUE, FALSE

  2, Second Corp., At Risk Alert, 10/7/2011, FALSE, TRUE

  2, Second Corp., At Risk Alert, 1/13/2013, TRUE, FALSE

  2, Second Corp., At Risk Alert, 2/12/2013, FALSE, TRUE

  2, Second Corp., At Risk Alert, 2/17/2013, TRUE, FALSE

];

Tmp:

Load

  *,

  if(Company_Id=previous(Company_Id) and previous(After_Value)='TRUE', previous(Time_Stamp)) as StartDate,

  Time_Stamp as EndDate

resident Data where Before_Value='TRUE' order by Company_Id, Time_Stamp asc;

drop table Data;

MVP
MVP

Re: Start date and end date in two different rows

result

1.png


script

a1:

load * INLINE [

  Company_Id, Company_Name, Field_Label, Time_Stamp, Before_Value, After_Value

  1, Lulz Corp., At Risk Alert, 7/26/2011, FALSE, TRUE

  1, Lulz Corp., At Risk Alert, 10/1/2011, TRUE, FALSE

  1, Lulz Corp., At Risk Alert, 10/7/2011, FALSE, TRUE

  1, Lulz Corp., At Risk Alert, 1/13/2013, TRUE, FALSE

  1, Lulz Corp., At Risk Alert, 2/12/2013, FALSE, TRUE

  1, Lulz Corp., At Risk Alert, 2/17/2013, TRUE, FALSE

  1, Lulz Corp., At Risk Alert, 4/12/2013, FALSE, TRUE

  1, Lulz Corp., At Risk Alert, 6/24/2013, TRUE, FALSE

  1, Lulz Corp., At Risk Alert, 7/3/2013, FALSE, TRUE

  1, Lulz Corp., At Risk Alert, 7/29/2014, TRUE, FALSE

  1, Lulz Corp., At Risk Alert, 8/18/2014, FALSE, TRUE

  1, Lulz Corp., At Risk Alert, 8/19/2014, TRUE, FALSE

];

a: noconcatenate

load if(even(rowno()), rowno()-1, rowno()) as id, rowno() as id2, *

Resident a1 order by Company_Id, Time_Stamp;

drop table a1;

t:

noconcatenate

load id, Company_Id, Company_Name, Field_Label, Time_Stamp as End//, Before_Value, After_Value

Resident a

where  Before_Value = 'TRUE' and  After_Value='FALSE';

join (t)

load id, Company_Id, Time_Stamp as Start //, Before_Value, After_Value

Resident a

where  Before_Value = 'FALSE' and  After_Value='TRUE';

drop table a;

Re: Re: Start date and end date in two different rows

Hi,

maybe like this:

QlikCommunity_Thread_142617_Pic1.JPG.jpg

tabInput:

LOAD * Inline [

    Company_Id, Company_Name, Field_Label, Time_Stamp, Before_Value, After_Value

    1, Lulz Corp., At Risk Alert, 7/26/2011, FALSE, TRUE

    1, Lulz Corp., At Risk Alert, 10/1/2011, TRUE, FALSE

    1, Lulz Corp., At Risk Alert, 10/7/2011, FALSE, TRUE

    1, Lulz Corp., At Risk Alert, 1/13/2013, TRUE, FALSE

    1, Lulz Corp., At Risk Alert, 2/12/2013, FALSE, TRUE

    1, Lulz Corp., At Risk Alert, 2/17/2013, TRUE, FALSE

    1, Lulz Corp., At Risk Alert, 4/12/2013, FALSE, TRUE

    1, Lulz Corp., At Risk Alert, 6/24/2013, TRUE, FALSE

    1, Lulz Corp., At Risk Alert, 7/3/2013, FALSE, TRUE

    1, Lulz Corp., At Risk Alert, 7/29/2014, TRUE, FALSE

    1, Lulz Corp., At Risk Alert, 8/18/2014, FALSE, TRUE

    1, Lulz Corp., At Risk Alert, 8/30/2014, TRUE, FALSE

    2, ACME Inc., At Risk Alert, 8/26/2011, FALSE, TRUE

    2, ACME Inc., At Risk Alert, 11/1/2011, TRUE, FALSE

    2, ACME Inc., At Risk Alert, 11/7/2011, FALSE, TRUE

    2, ACME Inc., At Risk Alert, 2/13/2013, TRUE, FALSE

    2, ACME Inc., At Risk Alert, 3/12/2013, FALSE, TRUE

    2, ACME Inc., At Risk Alert, 3/17/2013, TRUE, FALSE

    2, ACME Inc., At Risk Alert, 5/12/2013, FALSE, TRUE

    2, ACME Inc., At Risk Alert, 7/24/2013, TRUE, FALSE

    2, ACME Inc., At Risk Alert, 8/3/2013, FALSE, TRUE

    2, ACME Inc., At Risk Alert, 8/29/2014, TRUE, FALSE

    2, ACME Inc., At Risk Alert, 9/18/2014, FALSE, TRUE

    2, ACME Inc., At Risk Alert, 9/30/2014, TRUE, FALSE

];

tabTemp:

LOAD RangeSum(Peek(ID),-(not Before and After)) as ID,

    Company_Id,

    Company_Name,

    Field_Label,

    If(not Before and After, 'Begin', If(Before and not After, 'End')) as Time_Stamp_Type,

    Time_Stamp;  

LOAD *,

    Evaluate(Before_Value&'()') as Before,

    Evaluate(After_Value&'()') as After

Resident tabInput

Order By Company_Id, Company_Name, Field_Label, Time_Stamp;

tabOutput:

Generic LOAD * Resident tabTemp;

DROP Table tabInput, tabTemp;

hope this helps

regards

Marco

Not applicable

Re: Re: Start date and end date in two different rows

Thank you gentlemen! All of your answers worked on the example data I gave, but the answer I selected handled open alerts (that is, alerts with only a beginning date) better.

I really appreciate it!

Not applicable

Re: Start date and end date in two different rows

EDIT: NEVERMIND. Thanks!

Re: Re: Start date and end date in two different rows

Hi,

I already worked on it before seeing your edit:

Using some wonderful code from Rob Wunderlich I combined the generic tables into one.

I also changed your calendar script a bit.

The result looks like this:

QlikCommunity_Thread_142617_Pic2.JPG.jpg

tabInput:

LOAD * Inline [

    Company_Id, Company_Name, Field_Label, Time_Stamp, Before_Value, After_Value

    1, Lulz Corp., At Risk Alert, 7/26/2011, FALSE, TRUE

    1, Lulz Corp., At Risk Alert, 10/1/2011, TRUE, FALSE

    1, Lulz Corp., At Risk Alert, 10/7/2011, FALSE, TRUE

    1, Lulz Corp., At Risk Alert, 1/13/2013, TRUE, FALSE

    1, Lulz Corp., At Risk Alert, 2/12/2013, FALSE, TRUE

    1, Lulz Corp., At Risk Alert, 2/17/2013, TRUE, FALSE

    1, Lulz Corp., At Risk Alert, 4/12/2013, FALSE, TRUE

    1, Lulz Corp., At Risk Alert, 6/24/2013, TRUE, FALSE

    1, Lulz Corp., At Risk Alert, 7/3/2013, FALSE, TRUE

    1, Lulz Corp., At Risk Alert, 7/29/2014, TRUE, FALSE

    1, Lulz Corp., At Risk Alert, 8/18/2014, FALSE, TRUE

    1, Lulz Corp., At Risk Alert, 8/30/2014, TRUE, FALSE

    2, ACME Inc., At Risk Alert, 8/26/2011, FALSE, TRUE

    2, ACME Inc., At Risk Alert, 11/1/2011, TRUE, FALSE

    2, ACME Inc., At Risk Alert, 11/7/2011, FALSE, TRUE

    2, ACME Inc., At Risk Alert, 2/13/2013, TRUE, FALSE

    2, ACME Inc., At Risk Alert, 3/12/2013, FALSE, TRUE

    2, ACME Inc., At Risk Alert, 3/17/2013, TRUE, FALSE

    2, ACME Inc., At Risk Alert, 5/12/2013, FALSE, TRUE

    2, ACME Inc., At Risk Alert, 7/24/2013, TRUE, FALSE

    2, ACME Inc., At Risk Alert, 8/3/2013, FALSE, TRUE

    2, ACME Inc., At Risk Alert, 8/29/2014, TRUE, FALSE

    2, ACME Inc., At Risk Alert, 9/18/2014, FALSE, TRUE

    2, ACME Inc., At Risk Alert, 9/30/2014, TRUE, FALSE

];

tabTemp:

LOAD RangeSum(Peek(ID),-(not Before and After)) as ID,

    Company_Id,

    Company_Name,

    Field_Label,

    If(not Before and After, 'Begin', If(Before and not After, 'End')) as Time_Stamp_Type,

    Date(Time_Stamp,'MM/DD/YYYY');  

  LOAD *,

    Evaluate(Before_Value&'()') as Before,

    Evaluate(After_Value&'()') as After

Resident tabInput

Order By Company_Id, Company_Name, Field_Label, Time_Stamp;

tabCompRisk:

LOAD Distinct ID Resident tabTemp;

tabOutput:

Generic LOAD * Resident tabTemp;

DROP Table tabInput, tabTemp;

FOR i = NoOfTables()-1 to 0 STEP -1

  LET vTable=TableName($(i));

  IF WildMatch('$(vTable)', 'tabOutput.*') THEN

    LEFT JOIN (tabCompRisk) LOAD * RESIDENT [$(vTable)];

    DROP TABLE [$(vTable)];

  ENDIF

NEXT i

BeginCalendar:

Load Begin,

    Week(Begin) As Begin_Week,

    Year(Begin) As Begin_Year,

    Month(Begin) As Begin_Month,

    Day(Begin) As Begin_Day,

    YeartoDate(Begin)*-1 as Begin_CurYTDFlag,

    YeartoDate(Begin,-1)*-1 as Begin_LastYTDFlag,

    InYear(Begin, Monthstart(maxDate),-1) as Begin_RC12,

    Date(MonthStart(Begin), 'MMM-YYYY') as Begin_MonthYear,

    Dual('Q'&Ceil(Month(Begin)/3),Ceil(Month(Begin)/3)) as Begin_Quarter,

    Dual(Week(WeekStart(Begin))&'-'&WeekYear(Begin),WeekStart(Begin)) as Begin_WeekYear,

    WeekDay(Begin) as Begin_WeekDay;

LOAD Date(minDate+IterNo()-1) as Begin,

    maxDate

While minDate+IterNo()-1<=maxDate;  

LOAD Min(Begin) as minDate,

    Max(Begin) as maxDate

Resident tabCompRisk;

EndCalendar:

Load End,

    Week(End) As End_Week,

    Year(End) As End_Year,

    Month(End) As End_Month,

    Day(End) As End_Day,

    YeartoDate(End)*-1 as End_CurYTDFlag,

    YeartoDate(End,-1)*-1 as End_LastYTDFlag,

    InYear(End, Monthstart(maxDate),-1) as End_RC12,

    Date(MonthStart(End), 'MMM-YYYY') as End_MonthYear,

    Dual('Q'&Ceil(Month(End)/3),Ceil(Month(End)/3)) as End_Quarter,

    Dual(Week(WeekStart(End))&'-'&WeekYear(End),WeekStart(End)) as End_WeekYear,

    WeekDay(End) as End_WeekDay;

LOAD Date(minDate+IterNo()-1) as End,

    maxDate

While minDate+IterNo()-1<=maxDate;  

LOAD Min(End) as minDate,

    Max(End) as maxDate

Resident tabCompRisk;

hope it helps nevertheless

regards

Marco

Community Browser