14 Replies Latest reply: Feb 23, 2018 8:00 AM by Mark Little RSS

    Time Dimensions Difference

    Michael Anywar

      Dear all, I need help on this, hoping its possible

       

      IDStart Date *Time Stamp1End Date *TimeStamp2Name
      112/2/2012 12:22:4212/2/2012 12:01:21Activity1
      212/2/2012 12:01:2112/3/2012 16:01:21Activity 2
      312/3/2012 16:01:2112/6/2012 12:01:21Activity 3
      412/6/2012 12:01:21....Activity 4

      I would like to get the values of End Date (TimeStamp 2) taking into consideration that the values of TimeStamp2 are dependent on the valueTime Stamp1 of the next row and are same values only that they are values of the the second Row.

       

      SO basically the first value of Row 1  TimeStamp2 would be 12/2/2012 12:01:21 provided the ID "2" is the one that comes next after ID "1" 


       

        • Re: Time Dimensions Difference
          Kaushik Solanki

          Hi Assuming that these are the fields of your table you can try below script.

           

          Data:

          Load ID,StartDate,EndDate,Name

          From XYZ;

           

          Left join (Data)

          Load StartDate as EndDate, ID as NextID

          From xyz;

           

          Regards,

          Kaushik Solanki

            • Re: Time Dimensions Difference
              Michael Anywar

              I forgot to mention, that the field TimeStamp 2 does not exist, so if I load it now, it will tell me the field  does not exists.

               

               

              Probably i can re phase my problem that i need to create/ Add another field called End date with the specified data.

                • Re: Time Dimensions Difference
                  Kaushik Solanki

                  Try this then.

                   

                  Load Date as StartDate,Previous(Date) as EndDate

                  From xyz

                  order by Date Desc;

                   

                  Regards,

                  Kaushik Solanki

                    • Re: Time Dimensions Difference
                      Michael Anywar

                      Hello Solanki but Previous(date) dooes not exist. See table below. How I would like the out come to be.

                      ID and Start  have unique values so i need to make the values of field (Start) Mapped to a New field (End), provided that ID belongs to the same Filed (Name( and Field (Start) in the next Row.

                      IDStartEndName
                      A12Name1
                      B23Name1
                      C3Name1
                      D49Name2
                      E57Name 3
                      F9Name 2
                      G710Name3
                      H10Name 3

                      NOTE: Field (End ) does not exist in the Master Table.

                        • Re: Time Dimensions Difference
                          Mark Little

                          Hi Micheal,

                           

                          You want to look at the Peek Function.

                           

                          Something Like the below

                          LOAD

                               ID

                               START

                               IF(ROWNO() =1,

                                    '',

                                    PEEK(START,-1)

                               ) AS END

                               NAME;

                          FROM TABLE

                          ORDER BY ID desc;

                           

                          The order by is important as you want the table in the reverse order.

                           

                          Mark

                            • Re: Time Dimensions Difference
                              Michael Anywar

                              Hi Mark, am not sure if the table is in Reverse, if we take it the way you suggest, then it will populate End that belongs to Name1 with values that belong to Name2, for example where ID is C in the previous table i have provided, yet END for ID C is supposed to be empty as Name1 didnt have any activity following ID C

                                • Re: Time Dimensions Difference
                                  Mark Little

                                  Hi,

                                   

                                  Try the Below. Attached test.qvw

                                   

                                  DATA:

                                  LOAD * Inline

                                  [ID, START, NAME

                                  A, 1, Name1

                                  B, 2, Name1

                                  C, 3, Name1

                                  D, 4, Name2

                                  E, 5, Name3

                                  F, 9, Name2

                                  G, 7, Name3

                                  H, 10, Name3

                                  ];

                                   

                                  NoConcatenate

                                   

                                   

                                  NEW:

                                  LOAD

                                      ID,

                                      START,

                                      IF(ROWNO() =1,

                                            '',

                                            IF(PEEK(NAME) = NAME,

                                            PEEK(START,-1),

                                            '')

                                      ) AS END,

                                      NAME

                                  Resident DATA

                                  ORDER BY NAME, ID desc;

                                   

                                   

                                  DROP TABLE DATA;

                                    • Re: Time Dimensions Difference
                                      Michael Anywar

                                      Hi Mark, would the load work if I had Thousands of records in the Master table..?

                                       

                                      Probably the loading inline of the data would take lots of resources...

                                      So am skeptical on loading the data inline..

                                       

                                       

                                      [ID, START, NAME];

                                       

                                      NoConcatenate

                                       

                                       

                                      NEW:

                                      LOAD

                                          ID,

                                          START,

                                          IF(ROWNO() =1,

                                                '',

                                                IF(PEEK(NAME) = NAME,

                                                PEEK(START,-1),

                                                '')

                                          ) AS END,

                                          NAME

                                      Resident DATA

                                      ORDER BY NAME, ID desc

                                      • Re: Time Dimensions Difference
                                        Michael Anywar

                                        Could I be doing something Wrong..

                                        This is Based on my real Master table:

                                         

                                         

                                        [TaskActivities]:

                                        LOAD

                                        Timestamp(ConvertToLocalTime((25569 + Round([ts]/1000) / 86400),'GMT+02:00')) AS [Start],

                                        [fullName] AS [fullName],

                                        [activityType] AS [activityType],

                                        [taskType] AS [taskType],

                                        [manufacturingOrderId] AS [manufacturingOrderId],

                                        [productId] AS [productId],

                                        [taskId] AS [taskId]

                                           

                                        RESIDENT RestConnectorMasterTable;

                                         

                                         

                                        // Left Join(root)

                                        //     LOAD [ts] AS [End], [taskId] AS NextID

                                           

                                            NoConcatenate

                                         

                                         

                                        NEW:

                                        LOAD

                                            [taskId],

                                            Timestamp(ConvertToLocalTime((25569 + Round([ts]/1000) / 86400),'GMT+02:00')) AS [Start],

                                            IF(ROWNO() =1,

                                                  '',

                                                  IF(PEEK([fullName]) = [fullName],

                                                  PEEK(START,-1),

                                                  '')

                                            ) AS End_Date,

                                            [fullName],

                                            [taskType],

                                            [productId]

                                           

                                        Resident RestConnectorMasterTable

                                         

                                         

                                        ORDER BY End_Date, [taskId] desc;

                                         

                                         

                                         

                                        DROP TABLE RestConnectorMasterTable;

                                          • Re: Time Dimensions Difference
                                            Mark Little

                                            HI Michael,

                                             

                                            Where data come from should matter, I just didn't that for data to test with.

                                            The important ting is to get the data in the correct order to create the end date from.

                                            So how are you grouping together? The example i gave was fullName, because you only wanted to take the the prior start date when the fullName value matched. Then it was by start date. We use desc order, because peek(field,-1) look at previous value in that field and you wanted the next value, so reversing the order achieves that.

                                             

                                            So i would say that you want to Change the Order by to fullName, StartDate desc.

                                             

                                            Mark

                                              • Re: Time Dimensions Difference
                                                Michael Anywar

                                                Hello Mark,

                                                See below, I've tried but giving me errors still. I dodnt know if am asking too much..am just getting stuck...

                                                 

                                                RestConnectorMasterTable:

                                                SQL SELECT

                                                "ts",

                                                "fullName",

                                                "activityType",

                                                "taskType",

                                                "manufacturingOrderId",

                                                "productId",

                                                "taskId"

                                                FROM JSON (wrap on) "root"

                                                WITH CONNECTION(Url "££££££$%^^")

                                                 

                                                NEXT startAt;

                                                 

                                                 

                                                [root]:

                                                LOAD

                                                Timestamp(ConvertToLocalTime((25569 + Round([ts]/1000) / 86400),'GMT+02:00')) AS [StartTime],

                                                [fullName] AS [fullName],

                                                [activityType] AS [activityType],

                                                [taskType] AS [taskType],

                                                [manufacturingOrderId] AS [manufacturingOrderId],

                                                [productId] AS [productId],

                                                [taskId] AS [taskId]

                                                   

                                                RESIDENT RestConnectorMasterTable

                                                 

                                                // DROP TABLE RestConnectorMasterTable;

                                                 

                                                [root1]:

                                                Load

                                                 

                                                NoConcatenate

                                                    

                                                    [StartTime],

                                                    [fullName],

                                                    [activityType],

                                                   

                                                RESIDENT root;

                                                 

                                                 

                                                     Order by StartTime

                                                    

                                                     Drop Table root;

                                                    

                                                TaskActivities:

                                                 

                                                 

                                                LOAD

                                                     NoConcatenate

                                                    

                                                  [StartTime],

                                                  [StartTime] as [EndTime],

                                                    [fullName],

                                                    [activityType],

                                                    [manufacturingOrderId],

                                                    [taskType],

                                                    [taskId]

                                                   

                                                     Resident root1;

                                                    

                                                     group by fullname, EndTime;

                                                    

                                                     Drop table root1 ;

                                                  • Re: Time Dimensions Difference
                                                    Mark Little

                                                    HI Michael,

                                                     

                                                    No problems. Part of the issue is your NoConatenate should always come before you load statement not after, like a join.

                                                    QlikView will automatically concatenate tables that contain the same field, so when you don't want that like in the example we us NoConcatenate to stop this.

                                                     

                                                    You also seem to have removed the peek as well.

                                                     

                                                    Mark

                                        • Re: Time Dimensions Difference
                                          Kaushik Solanki

                                          Hi,

                                           

                                          Can you tell me what all fields you have?

                                           

                                          ID,Start and Name?

                                           

                                          Regards,
                                          Kaushik Solanki

                                            • Re: Time Dimensions Difference
                                              Michael Anywar

                                              [TaskActivities]:

                                              LOAD

                                              Timestamp(ConvertToLocalTime((25569 + Round([ts]/1000) / 86400),'GMT+02:00')) AS [Start],

                                              [fullName] AS [fullName],

                                              [activityType] AS [activityType],

                                              [taskType] AS [taskType],

                                              [manufacturingOrderId] AS [manufacturingOrderId],

                                              [productId] AS [productId],

                                              [taskId] AS [taskId]

                                                

                                              RESIDENT RestConnectorMasterTable;

                                               

                                               

                                              That is what I have. Now i need to add a new field End  basing on the criteria as I mentioned. Start and taskID  are Unique,