Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Ofkoz
New Contributor

Function Date

Hello gues,

Help me please.

I'm uploading the column in format:

01.01.2019;

03.01.2019

02.02.2019;

03.02.2019

How do I convert it to a format: January 2018;February 2019, with grouping by month and year.

Thanks.

column name - [End Date]

 

 

6 Replies
MindaugasBacius
Valued Contributor III

Re: Function Date

,month([End Date]) & ' ' & year([End Date]) as [Year month]
Please read about Date and Time functions:
https://help.qlik.com/en-US/qlikview/November2018/Subsystems/Client/Content/QV_QlikView/Scripting/Da...
Ofkoz
New Contributor

Re: Function Date

Thank you, but the values of January 2019 and February 2019 are not grouped. I get some data: January 2019;February, 2019;February, 2019;February, 2019; January 2019.

Highlighted
PV
New Contributor II

Re: Function Date

Try creating a new column for your 'End Date' using MonthStart()/ MonthEnd() functions and use that new column as input for the solution suggested above.
MonthStart([End Date])
However make sure that it is not impacting the rest of your model.
olivierrobin
Valued Contributor III

Re: Function Date

hello

you can use a preceding lod

first , load your data by adding a column year-month

then use a 2nd load with group by clause

MindaugasBacius
Valued Contributor III

Re: Function Date

,month([End Date]) + year([End Date]) * 100 as [Year month]

You will come up with something like this 201901, 201902.
Ofkoz
New Contributor

Re: Function Date

The current script is.

//**********Table1************
LOAD Date([End Date],month([End Date]) & ' ' & year([End Date])) as [Year month],

Status // the histogram takes into account the Y-axis - Count(Status)

FROM
[path\name file.xlsx]
(ooxml, embedded labels);

Tell me how to use the second load to create an additional column and use GROUP by?