7 Replies Latest reply: Nov 20, 2014 5:56 PM by Marco Wedel RSS

    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!

        • Re: Start date and end date in two different rows
          Marco Wedel

          a generic load with some conditions should work

            • Re: Re: Start date and end date in two different rows
              Marco Wedel

              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

            • Re: Start date and end date in two different rows
              Morgan Kejerhag

              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
                Massimo Grossi

                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

                  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: Re: Start date and end date in two different rows
                      Marco Wedel

                      Hi,

                       

                      I already worked on it before seeing your edit:

                       

                      Using some wonderful code from rwunderlich 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