Start date and end date in two different rows

[#1]

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.

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:

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!

[MarcoWedel]

a generic load with some conditions should work

[morgankejerhag]

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;

[maxgro]

result

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;

[MarcoWedel · Accepted solution]

Hi,

maybe like this:

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

[#6]

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!

[#7]

EDIT: NEVERMIND. Thanks!

[MarcoWedel]

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:

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