Issue loading a Time Field

[#1]

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.

[swuehl]

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.

[#3]

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

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

[swuehl · Accepted solution]

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];

[#5]

works great, thank you so much for your help, really appreciate it