Line chart question

[Anonymous]

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.

[vishsaggi]

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;

[rubenmarin]

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

[MarcoWedel]

please post some sample data and your expected result.

[Anonymous]

thank yo i,2ll try and answer

[Anonymous]

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.

[Anonymous]

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)

[big_dreams]

can you post your complete script??

Regards,

[Anonymous]

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

[jonathandienst]

>>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.