topic Re: How load date YYYY-MM in QlikView

How load date YYYY-MM

irmantas — Wed, 25 Nov 2020 16:16:04 GMT

Hello all,

From SQL I am loading data like this :

Year(INVOICEDATE) as Year,

Month(INVOICEDATE) as Month,

Day(INVOICEDATE) as Day,

But i need load field like this : YYYY-MM

I was try :Year(INVOICEDATE)&'-'& Month(INVOICEDATE) as YearMonth,

But result is 0

Is it posable???

Re: How load date YYYY-MM

Chanty4u — Mon, 22 Jan 2018 13:17:34 GMT

what is the Invoice date format?

Re: How load date YYYY-MM

Chanty4u — Mon, 22 Jan 2018 13:18:44 GMT

you can try this

Date(monthstart(Date), 'YYYY-MM') as MonthYear

Re: How load date YYYY-MM

irmantas — Mon, 22 Jan 2018 13:18:47 GMT

YYYY.MM.DD hh:mm:ss

Re: How load date YYYY-MM

Chanty4u — Mon, 22 Jan 2018 13:21:15 GMT

try

Date(InDate,'YYYY-MM') as YearMonth

Re: How load date YYYY-MM

irmantas — Mon, 22 Jan 2018 13:23:19 GMT

I got error:

'Date' is not a recognized built-in function name.

Re: How load date YYYY-MM

Chanty4u — Mon, 22 Jan 2018 13:27:44 GMT

used invoice date only right?

Re: How load date YYYY-MM

Anil_Babu_Samineni — Mon, 22 Jan 2018 13:30:16 GMT

My option is this?

Date(Date#(INVOICEDATE,'YYYY.MM.DD hh:mm:ss'), 'YYYY-MM')

Re: How load date YYYY-MM

irmantas — Mon, 22 Jan 2018 13:30:30 GMT

Date(INVOICEDATE,'YYYY-MM') as YearMonth

Re: How load date YYYY-MM

irmantas — Mon, 22 Jan 2018 13:39:23 GMT

Error: 'Date#' is not a recognized built-in function name

Re: How load date YYYY-MM

Anil_Babu_Samineni — Mon, 22 Jan 2018 13:42:37 GMT

Which version of QV you are with? And where you are trying? Can you show image atleast

Re: How load date YYYY-MM

irmantas — Mon, 22 Jan 2018 14:12:34 GMT

My QV version 12.0

I need dimension for filtering 'Year-Month'

My load script is:

Re: How load date YYYY-MM

Anil_Babu_Samineni — Mon, 22 Jan 2018 14:15:35 GMT

Date# functions works only QV not for SQL, Anyway, Can you try this?

ODBC..

Load *, Date(Date#(....));

SQL SELECT * FROM ..;

Re: How load date YYYY-MM

irmantas — Mon, 22 Jan 2018 14:49:12 GMT

You mean:

But result is BLANK

Re: How load date YYYY-MM

Anil_Babu_Samineni — Mon, 22 Jan 2018 14:51:42 GMT

Give, Between space from Load and * like

Load *, Date....

Re: How load date YYYY-MM

irmantas — Mon, 22 Jan 2018 14:56:27 GMT

The same, no data in YearMonth field.

Re: How load date YYYY-MM

Anil_Babu_Samineni — Mon, 22 Jan 2018 15:10:11 GMT

Image please for DateField?

Re: How load date YYYY-MM

andrei_delta — Mon, 22 Jan 2018 15:27:20 GMT

hi,

this is the sql command for getting YYYY-MM from current time : convert(varchar(7), getdate(), 126)

Re: How load date YYYY-MM

irmantas — Tue, 23 Jan 2018 07:55:46 GMT

Working:

convert(varchar(7), INVOICEDATE, 126) as YearMonth,

Thank you