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

[khorosnt]

Hi all!

I have loaded the file wich has next structure:

Indicator	Plant	Jan	Feb	...
Indicator name 1	plant1	20	34
Indicator name 2	plant2	30	12

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

KPIMonth	Date
Jan	11/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 )

[OmarBenSalem]

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]

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]

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]

yes I will try.

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