Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Issue loading a Time Field

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.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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

View solution in original post

13 Replies
swuehl
MVP
MVP

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

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

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

Not applicable
Author

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.

swuehl
MVP
MVP

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

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

swuehl
MVP
MVP

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

swuehl
MVP
MVP

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

Not applicable
Author

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