Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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];
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
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
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];
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";
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.
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];
Yes, as Stefan mentioned, I forgot to add the NoConcatenate in the resident load. Try and see if that resolves the issue for you
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];
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];
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).