topic Start date and end date in two different rows in QlikView

Start date and end date in two different rows

Tue, 18 Nov 2014 18:28:05 GMT

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!

Re: Start date and end date in two different rows

MarcoWedel — Tue, 18 Nov 2014 18:56:56 GMT

a generic load with some conditions should work

Re: Start date and end date in two different rows

morgankejerhag — Tue, 18 Nov 2014 19:37:20 GMT

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;

Re: Start date and end date in two different rows

maxgro — Tue, 18 Nov 2014 20:31:24 GMT

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;

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

MarcoWedel — Tue, 18 Nov 2014 22:15:59 GMT

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

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

Wed, 19 Nov 2014 13:22:29 GMT

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!

Re: Start date and end date in two different rows

Thu, 20 Nov 2014 20:43:45 GMT

EDIT: NEVERMIND. Thanks!

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

MarcoWedel — Thu, 20 Nov 2014 22:56:46 GMT

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