13 Replies Latest reply: May 18, 2016 3:57 AM by Simon Hynd RSS

    Issue loading a Time Field

    Simon Hynd

      Hi

       

      I'm trying to load 2 tables (Electrical Consumption and Transactions) to allow me to see relationship between electrical usage and items bought.

      I'm struggling to load the TransTimeCorr into the correct format from the TransItems Table as it does not relate to the time format in the Electrical Consumption Table.

       

      The TransItems table comes from a EPOS database so I can not change field settings. For some reason the Time field for each transaction is stored as : '30/12/1899 HH:MM:SS'  To try and get around this I have added

      Right([Time],8) AS [TransTimeCorr] into the data load editor but doesn't seem to have done the trick.

       

      As you can see from the app I am trying to plot the electrical consumption against the sum of quantity sold and sum of sales value but I'm struggling.

       

      Thanks for reading.

        • Re: Issue loading a Time Field
          Stefan Wühl

          This will probably create a text value which will not link correctly to dual time values in another table:

           

          Right([Time],8) AS [TransTimeCorr]

           

          See also

          Data Types in QlikView

          Get the Dates Right

          Why don’t my dates work?

           

          I think all you need to do is to create a dual time value like

           

          Time(Frac([Time])) as [TransTimecorr]

           

          or maybe just

          Time([Time]) as [TransTimeCorr]

           

          Besides this, I would reconsider loading your crosstable without aliasing the field names, then use a following resident load of your CROSSTABLE loaded table to format the field values as Time, using something like

           

          Time(Num#(FIELD) ) as TransTimeCorr

           

          I am also not sure if your key using date and time is chosen well here, but that's hard to tell without knowing your data (model) and requirements.

           

          Hope this helps,

          Stefan

          • Re: Issue loading a Time Field
            Sunny Talwar

            Can you try this and see if this fixes the issue?

             

            [Electrical Consumption]:

            CrossTable(TransTimeCorr,ElecConsum,2)

            LOAD

              [Date] AS [TransDate],

              [Day Total:] AS [ElecConDayTotal:],

              [3.1225022567582503E-17] AS [00:00:00],

              [2.0833333333333332E-2] AS [00:30:00],

              [4.1666666666666664E-2] AS [01:00:00],

              [6.25E-2] AS [01:30:00],

              [8.3333333333333301E-2] AS [02:00:00],

              [0.104166666666667] AS [02:30:00],

              [0.125] AS [03:00:00],

              [0.14583333333333301] AS [03:30:00],

              [0.16666666666666599] AS [04:00:00],

              [0.1875] AS [04:30:00],

              [0.20833333333333301] AS [05:00:00],

              [0.22916666666666599] AS [05:30:00],

              [0.25] AS [06:00:00],

              [0.27083333333333298] AS [06:30:00],

              [0.29166666666666602] AS [07:00:00],

              [0.3125] AS [07:30:00],

              [0.33333333333333298] AS [08:00:00],

              [0.35416666666666602] AS [08:30:00],

              [0.375] AS [09:00:00],

              [0.39583333333333298] AS [09:30:00],

              [0.41666666666666602] AS [10:00:00],

              [0.4375] AS [10:30:00],

              [0.45833333333333298] AS [11:00:00],

              [0.47916666666666602] AS [11:30:00],

              [0.5] AS [12:00:00],

              [0.52083333333333304] AS [12:30:00],

              [0.54166666666666596] AS [13:00:00],

              [0.5625] AS [13:30:00],

              [0.58333333333333304] AS [14:00:00],

              [0.60416666666666596] AS [14:30:00],

              [0.625] AS [15:00:00],

              [0.64583333333333304] AS [15:30:00],

              [0.66666666666666596] AS [16:00:00],

              [0.6875] AS [16:30:00],

              [0.70833333333333304] AS [17:00:00],

              [0.72916666666666596] AS [17:30:00],

              [0.75] AS [18:00:00],

              [0.77083333333333304] AS [18:30:00],

              [0.79166666666666596] AS [19:00:00],

              [0.8125] AS [19:30:00],

              [0.83333333333333304] AS [20:00:00],

              [0.85416666666666596] AS [20:30:00],

              [0.875] AS [21:00:00],

              [0.89583333333333304] AS [21:30:00],

              [0.91666666666666596] AS [22:00:00],

              [0.9375] AS [22:30:00],

              [0.95833333333333304] AS [23:00:00],

              [0.97916666666666596] AS [23:30:00]

            FROM [lib://Working Master DB/Electrical Consumption.xlsx]

            (ooxml, embedded labels, table is [Electrical Consumption]);

             

            [Electrical Consumption Final]:

            LOAD Time(Time#(TransTimeCorr, 'hh:mm:ss')) as TransTimeCorr,

              ElecConsum,

              [TransDate],

              [ElecConDayTotal:]

            Resident [Electrical Consumption];

             

            DROP Table [Electrical Consumption];

              • Re: Issue loading a Time Field
                Sunny Talwar

                It seems like you are trying to fix the time on TransItems table,

                 

                Try this:

                [TransItems]:

                LOAD

                  [TransNo] AS [TransNo],

                  [ItemNo] AS [itemID],

                  [Date] AS [TransDate],

                  [Time] AS [TransTime],

                  [Qty] AS [Qty],

                  [Val] AS [Val],

                  [ECR] AS [ECR],

                Time(Frac([Time])) as [TransTimeCorr];

                SQL SELECT

                "TransNo",

                  "ItemNo",

                  "Date",

                  "Time",

                  "Qty",

                  "Val",

                  "ECR"

                FROM "C:\Users\Simon\Documents\Dropbox (Neptune)\Dropbox (Neptune)\Qlik Models\Databases\Fidelity\Converted\JournalData.accdb"."TransItems";

                  • Re: Issue loading a Time Field
                    Simon Hynd

                    This has fixed the time formatting for the TransItems table data which now works well but lost the data from the Electrical Consumption table completely and can not see the Electrical Consumption Final Table (if there should now be one). 

                     

                    Expression editor is now only showing Date Info and TransItem tables. - sorry this is probably something really simple but I am a novice

                     

                    Thanks everybody for taking the time to reply too - really appreciated.

                      • Re: Issue loading a Time Field
                        Stefan Wühl

                        Use a NOCONCATENATE LOAD prefix

                         

                        [Electrical Consumption Final]:

                        NOCONCATENATE

                        LOAD Time(Time#(TransTimeCorr, 'hh:mm:ss')) as TransTimeCorr,

                          ElecConsum,

                          [TransDate],

                          [ElecConDayTotal:]

                        Resident [Electrical Consumption];

                         

                        DROP Table [Electrical Consumption];

                          • Re: Issue loading a Time Field
                            Sunny Talwar

                            Yes, as Stefan mentioned, I forgot to add the NoConcatenate in the resident load. Try and see if that resolves the issue for you

                            • Re: Issue loading a Time Field
                              Stefan Wühl

                              Also, be cautious when linking two fields with floating point values.

                               

                              For testing, try to link by text values only. Try your original script with only a single change:

                               

                              Text(Time(Frac([Time]))) as [TransTimeCorr];

                                • Re: Issue loading a Time Field
                                  Stefan Wühl

                                  But even then, you one table shows time values like

                                   

                                  10:07:30 or 09:56:15

                                   

                                  and your other table shows only half hours, like 14:30:00

                                   

                                  How do you expect these time values to match?

                                   

                                  Maybe you need to Round time values of the first table to half hour?

                                   

                                  Text(Time(Round(Frac([Time]),Maketime(0,30)))) as [TransTimeCorr];

                                  • Re: Issue loading a Time Field
                                    Simon Hynd

                                    Doesn't seem to be working - getting an error.

                                     

                                    Without changing the time to text I am getting a graph as below which is missing out lots of the 30min interval electrical readings. and nothing before 10:30am or after 16:30pm. ( changed the time interval on the x axis to 10min but that made no difference). ab40a59fad08480db9f9741950230e76.jpg

                                      • Re: Issue loading a Time Field
                                        Stefan Wühl

                                        What error do you get?

                                         

                                        Please note that all above is just a demo of what I was think about, since I can't reload your data, I haven't tested that the syntax is 100% correct (It's always good to post sample files that can be reloaded )

                                         

                                        As I tried to explain, I think you need to round your key values in the first file to create matches to the second.

                                          • Re: Issue loading a Time Field
                                            Simon Hynd

                                            Still stuck on this. Not getting an error now but getting the following chart which is not plotting values on the same timescale: e84bc1aac1034aba84be1dc9266aa36b.jpg

                                            When i change the time dimension to '=time(class(TransTimeCorr,MakeTime(0,30)))' I get the correct energy consumption graph but the sum(qty) just shows 0.

                                             

                                            The only reason I am linking the times together as a key is so I can plot them on same chart axis to see correlation between quantity of sales and electrical usage. I also have other tables which are not imported at moment which also link the same time key for number of admissions, labour cost per hour etc so need time to relate together but there won't necessarily be exactly same time when different transactions / consumption / labour spend happens - hope this makes sense. Do I not need to link time together as a key in the tables? Again really sorry if I'm asking silly questions - I've watched all the videos, read 2 books on Qlik Sense (Cookbook and user guide) and still not picking everything up.

                                             

                                            I've also attached the app showing the settings

                                              • Re: Issue loading a Time Field
                                                Stefan Wühl

                                                Hm, maybe the different suggestions were kind of confusing.

                                                 

                                                I've suggested to link your TransTimeCorr field by text value and only change a single line compared to your original file (since I think the Crosstable should return text values for TransTimeCorr).

                                                 

                                                Hence, in your current app, you would need to remove / comment this part:

                                                 

                                                 

                                                [Electrical Consumption Final]:

                                                NoConcatenate

                                                LOAD Time(Time#(TransTimeCorr, 'hh:mm:ss')) as TransTimeCorr,

                                                  ElecConsum,

                                                  [TransDate],

                                                  [ElecConDayTotal:]

                                                Resident [Electrical Consumption];

                                                 

                                                DROP Table [Electrical Consumption];