21 Replies Latest reply: May 25, 2014 6:56 PM by Marco Wedel RSS

    Fill missing data

      I am analysing some connection stats and need to know how many unique users are logged on each day. Some sample data is attached.

       

      The problem I have is that a user's session may start on a Monday and end on a Friday, therefore I need to include all days from when their session started, through to when it ended.

       

      How on earth do i go about doing that?

       

      Can I do it in chart? Or do I need to do something in the dataload beforehand?

        • Re: Fill missing data
          Srikanth P

          Hi Alex, Create the Calendar table for Max and Min date in the transactions table and remove the date if it is Sunday or Sat.

            • Re: Fill missing data
              Massimo Grossi

              in script you can adapt this (generate all dates between start and end date)

              Generating Missing Data In QlikView

              pag. 10

                  • Re: Fill missing data

                    Hi Henric,

                     

                    this is great. Thanks for your help AGAIN! I get the following error message though

                     

                    Error in expression:

                    ')' expected

                    UserSessionDetails_x_Dates:

                    Load SessionId,

                              Date( SessionStartedDate + IterNo() – 1 ) as ReferenceDate

                              Resident UserSessionDetails

                              While IterNo() <= SessionClosedDate - SessionStartedDate + 1

                     

                     

                     

                     

                     

                    This is the script I am using. What could I be doing wrong?

                     

                     

                     

                    UserSessionDetails:

                    LOAD UserName,

                        SessionId,

                        Date(DayStart(Date(SessionStarted,'DD/MM/YYYY')),'YYYY-MM-DD') As SessionStartedDate,

                        SessionStarted,

                        Date(DayStart(Date(SessionClosed,'DD/MM/YYYY')),'YYYY-MM-DD') As SessionClosedDate,

                        SessionClosed,

                        Firm,

                    SQL SELECT *

                    FROM dbo.usersessiondetails;

                     

                     

                    UserSessionDetails_x_Dates:

                    Load UserName,

                              Date( SessionStartedDate + IterNo() – 1 ) as ReferenceDate

                              Resident UserSessionDetails

                              While IterNo() <= SessionClosedDate - SessionStartedDate + 1 ;

                      • Re: Fill missing data
                        jagan mohan rao appala

                        HI,

                         

                        Check this

                         

                        UserSessionDetails:

                        LOAD UserName,

                            SessionId,

                            Date(DayStart(Date(SessionStarted,'DD/MM/YYYY')),'YYYY-MM-DD') As SessionStartedDate,

                            SessionStarted,

                            Date(DayStart(Date(SessionClosed,'DD/MM/YYYY')),'YYYY-MM-DD') As SessionClosedDate,

                            SessionClosed,

                            Firm;

                        SQL SELECT *

                        FROM dbo.usersessiondetails;

                         

                         

                        UserSessionDetails_x_Dates:

                        Load UserName,

                                  Date( SessionStartedDate + IterNo() – 1 ) as ReferenceDate

                                  Resident UserSessionDetails

                                  While IterNo() <= SessionClosedDate - SessionStartedDate + 1 ;

                         

                        You need to give ; after firm in above script.

                         

                        Regards,

                        Jagan.

                • Re: Fill missing data
                  Marco Wedel

                  Hi Alex,

                   

                  one possible solution:

                   

                  QlikCommunity_Thread_119296_Pic4.JPG.jpg

                   

                  QlikCommunity_Thread_119296_Pic3.JPG.jpg

                   

                   

                  tabConnectionStats:
                  LOAD *,
                       AutoNumberHash128(SessionStartedDate, SessionClosedDate) as %SessionIntervalID;
                  LOAD SessionId, 
                       Username, 
                       DisconnectionReason, 
                       SessionStartedDate, 
                       If(IsNum(SessionClosedDate), SessionClosedDate, Today()) as SessionClosedDate
                  FROM [http://community.qlik.com/servlet/JiveServlet/download/532979-107411/sample%20data%20connection%20stats.xlsx]
                  (ooxml, embedded labels, table is Sheet1);
                  
                  tabCalendar:
                  LOAD
                    Date,
                    WeekName(Date) as Week,
                    MonthName(Date) as Month,
                    QuarterName(Date) as Quarter,
                    Year(Date) as Year;
                  LOAD
                    Date(MinDate+IterNo()-1) as Date
                  While MinDate+IterNo()-1<=MaxDate;
                  LOAD
                    Min(SessionStartedDate) as MinDate,
                    Max(SessionClosedDate) as MaxDate
                  Resident tabConnectionStats;
                  
                  tabDateLink:
                  IntervalMatch(Date)
                  LOAD Distinct
                    SessionStartedDate,
                    SessionClosedDate
                  Resident tabConnectionStats;
                  
                  Left Join (tabDateLink)
                  LOAD
                    SessionStartedDate,
                    SessionClosedDate,
                    AutoNumberHash128(SessionStartedDate, SessionClosedDate) as %SessionIntervalID
                  Resident tabDateLink;
                  
                  DROP Fields SessionStartedDate, SessionClosedDate From tabDateLink;
                  

                   

                   

                  hope this helps

                   

                  regards

                   

                  Marco

                  • Re: Fill missing data
                    Henric Cronström

                    You have "...Firm , SQL SELECT...". It should be "...Firm ; SQL SELECT...".

                     

                    HIC

                      • Re: Re: Fill missing data

                        Hi henric,

                         

                        As I mentioned to Jagan, that was actually a typo. My script does contain the ;

                         

                        The full script is:

                         

                         

                        My full script is below which still gives the error

                         

                        UserSessionDetails:

                        LOAD UserName,

                            SessionId,

                            Environment,

                            Replace(DisconnectionReason, 'ClientConnectionClosed. Reason:','') As DisconnectionReason,

                        Date(DayStart(Date(SessionStarted,'DD/MM/YYYY')),'YYYY-MM-DD') As SessionStartedDate,

                          MakeTime(Hour(SessionStarted), Minute(SessionStarted), Second(SessionStarted))  As SessionStartedTime,

                          Hour(SessionStarted) As SessionStartedHour,

                            SessionStarted,

                        Date(DayStart(Date(SessionClosed,'DD/MM/YYYY')),'YYYY-MM-DD') As SessionClosedDate,

                          MakeTime(Hour(SessionClosed), Minute(SessionClosed), Second(SessionClosed))  As SessionClosedTime,

                          Hour(SessionClosed) As SessionClosedHour,

                            SessionClosed,

                            FlashPlayer,

                            Firm,

                            IPAddress,

                            TestAccount,

                            Disconnection,

                            Reconnection,

                            GeneralOutage,

                            FirmOutage,

                            WorkingDayDisconnect,

                            WorkingDayReconnect;

                        SQL SELECT *

                        FROM dbo.usersessiondetails;

                         

                         

                        UserSessionDetails_x_Dates:

                        Load UserName,

                                  Date( SessionStartedDate + IterNo() – 1 ) as ReferenceDate

                                  Resident UserSessionDetails

                                  While IterNo() <= SessionClosedDate - SessionStartedDate + 1 ;

                        • Re: Fill missing data

                          I worked out the problem, the first dash wasn't recognised as a minus sign for some reason. Having copied it from your blog it pasted as a dash rather than a minus sign

                          • Re: Fill missing data

                            I have just realised a slight problem I have is that the SessionClosed date is sometimes blank.

                             

                            If I wanted to assume a blank value was today. How would I do that?

                              • Re: Fill missing data
                                Marco Wedel

                                If(IsNum(SessionClosedDate), SessionClosedDate, Today()) as SessionClosedDate 


                                just like used in my solution ...


                                regards


                                Marco

                                  • Re: Fill missing data

                                    Thanks Marco

                                     

                                    but how do I combine it with my existing script?

                                     

                                    UserSessionDetails:

                                    LOAD UserName,

                                        SessionId,

                                        Environment,

                                        Replace(DisconnectionReason, 'ClientConnectionClosed. Reason:','') As DisconnectionReason,

                                        Date(DayStart(Date(SessionStarted,'DD/MM/YYYY')),'YYYY-MM-DD') As SessionStartedDate,

                                      MakeTime(Hour(SessionStarted), Minute(SessionStarted), Second(SessionStarted))  As SessionStartedTime,

                                      Hour(SessionStarted) As SessionStartedHour,

                                        SessionStarted,

                                        Date(DayStart(Date(SessionClosed,'DD/MM/YYYY')),'YYYY-MM-DD') As SessionClosedDate,

                                      MakeTime(Hour(SessionClosed), Minute(SessionClosed), Second(SessionClosed))  As SessionClosedTime,

                                      Hour(SessionClosed) As SessionClosedHour,

                                        SessionClosed,

                                        FlashPlayer,

                                        Firm,

                                        IPAddress,

                                        TestAccount,

                                        Disconnection,

                                        Reconnection,

                                        GeneralOutage,

                                        FirmOutage,

                                        WorkingDayDisconnect,

                                        WorkingDayReconnect;

                                    SQL SELECT *

                                    FROM dbo.usersessiondetails;

                                     

                                     

                                     

                                    Sorry for all the questions. This is all a little new to me!

                                    • Re: Fill missing data

                                      I tried your way and also adpated this slightly Marco, but still no luck

                                       

                                      If(IsNull(SessionClosed), Now()),Date(DayStart(Date(SessionClosed,'DD/MM/YYYY')),'YYYY-MM-DD') as SessionClosedDate,

                                       

                                       

                                      The SessionClosed field is in the format DD/MM/YYYY HH:MM:SS