Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

khorosnt
New Contributor

How to convert month in format 'Jan' то а specific date during load

Hi all!

I have loaded the file wich has next structure:

IndicatorPlantJanFeb...
Indicator name 1plant12034
Indicator name 2plant23012

Using crosstable function during load.

Crosstable (KPIMonth,value,6)

LOAD

    Indicator,

    Plant,

    Jan,

    Feb,

      ....

Now I need to make Qlik Sense read Jan like a date (for example 11/01/18)

is there a way to do it?

The simplest is to create table with monthname and date and join them... but it's not the best I think...

KPIMonthDate
Jan11/01/18

Can anyone help me with it?

Oh yeah and I am learner so please explain it like if you do it to an idiot )

4 Replies
omarbensalem
Esteemed Contributor

Re: How to convert month in format 'Jan' то а specific date during load

YourTable:

Crosstable (KPIMonth,value,6)

LOAD

    Indicator,

    Plant,

    Jan,

    Feb...;

noconcatenate

load * , date(Date#(KPIMonth,'MMM'),'DD/MM') as monthStart;

drop table YourTable;

and ur second table;

create a new field aas follow

date(MonthStart(Date),'DD/MM') as monthStart;


that way, the 2 tables would be associated through this monthStart field.

khorosnt
New Contributor

Re: How to convert month in format 'Jan' то а specific date during load

Oh ok and now sorry

So the full script is:

TempKPI18:
Crosstable (KPIMonth,value,6)
LOAD
    F3 as PlantCategory,
    F4 as PlantName,
    F6 as SSQCDMN,
    Indicator2 as IndicatorName,
    Unit,
    F9,
    Jan,
    Feb,
    Mar,
    Apr,
    May,
    Jun,
    Jul,
    Aug,
    Sep,
    Oct,
    Nov,
    Dec

FROM [lib://Losses 2018/Scorecard 2018\Scorecard_Industrial.xlsm]
(ooxml, embedded labels, header is 3 lines, table is Base_Conso_Target);

And where should I place this load statement?

noconcatenate

load * , date(Date#(KPIMonth,'MMM'),'DD/MM') as monthStart

omarbensalem
Esteemed Contributor

Re: How to convert month in format 'Jan' то а specific date during load

TempKPI18:
Crosstable (KPIMonth,value,6)
LOAD
    F3 as PlantCategory,
    F4 as PlantName,
    F6 as SSQCDMN,
    Indicator2 as IndicatorName,
    Unit,
    F9,
    Jan,
    Feb,
    Mar,
    Apr,
    May,
    Jun,
    Jul,
    Aug,
    Sep,
    Oct,
    Nov,
    Dec

FROM [lib://Losses 2018/Scorecard 2018\Scorecard_Industrial.xlsm]
(ooxml, embedded labels, header is 3 lines, table is Base_Conso_Target);

KPI18:

NOconcatenate

load * , date(Date#(KPIMonth,'MMM'),'DD/MM') as monthStart

resident TempKPI18;


drop table TempKPI18;


and u add the second field date(MonthStart(Date),'DD/MM') as monthStart; in the second table where you have uyour Date field.

Hope this is clear?

khorosnt
New Contributor

Re: How to convert month in format 'Jan' то а specific date during load

yes I will try.

And in case if I just join them....I guess it's easier or it is not optimal?