topic Line chart question in QlikView

Line chart question

Anonymous — Thu, 17 May 2018 14:51:44 GMT

hello i want to create a line chart which counts the material produced every minute. I have an sql db with date and time (23.05.2017 23:30:10) for measure i will count a column. When i add the Date column from sql i see multiple dates as dimension. I need to see the number of produced products every minute. So i dont want to see date as dimension but minute.

I hope i am clear.

Re: Line chart question

vishsaggi — Thu, 17 May 2018 15:08:31 GMT

May be try this:

LOAD *,

Time(Timestamp(Timestamp#(TimeLoad, 'DD.MM.YYYY hh:mm:ss'),'DD.MM.YYYY hh:mm:ss'), 'mm') AS TimeOnly

From YourDataSourceTableName;

Re: Line chart question

rubenmarin — Thu, 17 May 2018 15:26:00 GMT

Hi Erhan, the is also the Minute() function:

Minute(FieldName) as Minute

Or maybe:

Minute(Timestamp#(FieldName, 'DD.MM.YYYY hh:mm:ss')) as Minute

Re: Line chart question

MarcoWedel — Thu, 17 May 2018 19:41:10 GMT

please post some sample data and your expected result.

Re: Line chart question

Anonymous — Fri, 18 May 2018 05:06:36 GMT

thank yo i,2ll try and answer

Re: Line chart question

Anonymous — Fri, 18 May 2018 05:34:21 GMT

Started loading data

Connecting to MaintenanceSQL Connected

The following error occurred:

No qualified path for file: ***

The error occurred here:

LOAD *, Time(Timestamp(Timestamp#(DateTime,'DD.MM.YYYY hh:mm:ss'),'DD.MM.YYYY hh:mm:ss'),'mm')AS Timeonly From [MaintenanceSQL]

Data has not been loaded. Please correct the error and try loading again.

my date coloum name in sql is DateTime, i wrote your code in a new section at data load editor.

Re: Line chart question

Anonymous — Fri, 18 May 2018 05:36:03 GMT

hello

maybe i just need a measure function for this. like take the four same minute as one and count the other column

currently my measure is count (reference)

Re: Line chart question

big_dreams — Fri, 18 May 2018 05:58:47 GMT

can you post your complete script??

Regards,

Re: Line chart question

Anonymous — Fri, 18 May 2018 06:16:19 GMT

DateTime Reference

2016-12-15 12:27:09.000 12341234

2016-12-15 12:27:23.000 12341234

2016-12-15 12:29:09.000 32123451

2016-12-15 12:29:22.000 32123451

2016-12-15 12:29:28.000 12341234

2016-12-15 12:29:42.000 32123451

2016-12-15 12:29:52.000 32123451

2016-12-15 12:30:06.000 45134123

2016-12-15 12:31:12.000 45134123

2016-12-15 12:31:25.000 45134123

i need to see 2 at minute 12.27 at 2016-12-15 , need to see 5 at minute 12:29 at 2016-12-15 and so on as a line chart

Re: Line chart question

jonathandienst — Fri, 18 May 2018 06:17:46 GMT

>>No qualified path for file: ***

Use the SQL statement to get data from a database table. You can use a preceding LOAD statement to then use QlikSense script functions. Assuming the datetime value is called DateTimeField:

LOAD *,

Date(Floor(DateTimeField)) as Date, // date from DateTimeField;

Interval(Round(Frac(DateTimeField), 1/1440), 'm') as TimeValue, // minutes in day (as a datetime numeric)

Minute(DateTimeField) as Minute; // minutes portion of hh:mm time

SQL SELECT *

From YourDataSourceTableName;

Adapt as required for the correct table and field names and SQL query.

Re: Line chart question

Anonymous — Fri, 18 May 2018 06:52:32 GMT

hello in addition to code i deleted seconds from data manager, because in dimension it still displayed seconds.

now the graphic is as below, there is still 4 from same minute . i need 1 value which will be 8 in the yellow marked area as 12:12:2016 01:03

it should be like this

Re: Line chart question

jonathandienst — Fri, 18 May 2018 07:03:50 GMT

Deleting seconds from the format does not change the underlying value, so you still have seconds value - they only look like minute values. To ensure proper minute values, round the datetime to the nearest minute

TimeStamp(Round(DateTime, 1/1440), 'yyyy.MM.dd hh:mm')

(1440 is the number of minutes in a 24h day)

Re: Line chart question

Anonymous — Fri, 18 May 2018 07:30:13 GMT

sorry i couldn't find the place to write this

TimeStamp(Round(DateTime, 1/1440), 'yyyy.MM.dd hh:mm')

is it the main section at data load editor SET .....

or the code you send(where exacly)

Re: Line chart question

Anonymous — Fri, 18 May 2018 07:49:04 GMT

i wrote it as dimension expression , the result is as below, i just dont need the seconds that is all

Re: Line chart question

jonathandienst — Fri, 18 May 2018 07:56:49 GMT

If you provide the full script, as has already been requested, I will be able to show you exactly where to put that statement. Use it for the fact table load statement (ie where you load DateTime).

Re: Line chart question

Anonymous — Fri, 18 May 2018 08:32:09 GMT

i copied the script below. the graph is currently as below. But counts wrong, normally at sql db there is 7 references at "2018.05.18 10:56" but graph counts 3.

dimension = TimeStamp(Round(DateTime, 1/1440), 'yyyy.MM.dd hh:mm')

measure = count(referans)

Re: Line chart question

jonathandienst — Fri, 18 May 2018 08:58:33 GMT

LOAD

[ID],

APPLYMAP( 'CELLNOMapping', [CELLNO]) AS [CELLNO],

TimeStamp(Round(DateTime, 1/1440), 'yyyy.MM.dd hh:mm') AS [DateTimeM],

Timestamp([DateTime] ,'YYYY-MM-DD hh:mm[:ss[.fff]]') AS [DateTime],

[Referans];

SQL SELECT "ID",

"CELLNO",

"DateTime",

"Referans"

FROM "MidProduct"."dbo"."URT_BASE";

Use DateTimeM as the chart dimension. You need DateTime to keep the association with the calendar.

Re: Line chart question

Anonymous — Fri, 18 May 2018 10:13:57 GMT

01:12:03

Output cleared.

01:12:05

---

Started loading data

---

The following error occurred:

ApplyMap error:

map_id not found

---

The error occurred here:

LOAD

[ID],

APPLYMAP( 'CELLNOMapping', [CELLNO]) AS [CELLNO],

TimeStamp(Round(DateTime, 1/1440), 'yyyy.MM.dd hh:mm') AS [DateTimeM],

Timestamp([DateTime] ,'YYYY-MM-DD hh:mm[:ss[.fff]]') AS [DateTime],

[Referans]

---

The following error occurred:

ApplyMap error:

map_id not found

---

The error occurred here:

---

Data has not been loaded. Please correct the error and try loading again.

01:12:08

App successfully saved.

Re: Line chart question

rubenmarin — Fri, 18 May 2018 10:38:57 GMT

Hi Erhan, the dates are different (different second), so it will be different values, to show only only dimension value per minute you need to create the field that has the exact same value for every minute. That is, removing seconds and miliseconds:

TimeStamp(Floor(Timestamp#(DateTime, 'YYYY-MM-DD hh:mm:ss.fff'), 1/1440)) as DateTime

Check attachment.

Re: Line chart question

Anonymous — Mon, 21 May 2018 14:31:12 GMT

hello it works with with your file. now i'm trying to put the code inside my dashboard