Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 )
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.
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
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?
yes I will try.
And in case if I just join them....I guess it's easier or it is not optimal?