Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi.
I have data that has months as month abbrevations in two languages.
I tried the following:
- load mapping table inside qlikview to map dutch abbrevations to eng abbrevations -> not worked
- ... to map dutch abbrevations to numbers -> not worked
Well it kinda worked, the problem comes when the month is may or mei or 5 .. it does not recognize that at all?!
I am trying to parse a datefield out of this..
What the Gorilla Monsoon is happening?
Here are the fields that I am using in editor script:
DutchEnglishMonthMapping:
mapping LOAD * Inline
[
Abbr, Number
maart, 3
mrt, 3
mei, 5
juni, 6
juli, 7
okt, 10
jan, 1
feb, 2
mar, 3
apr, 4
may, 5
june, 6
july, 7
aug, 8
sept, 9
nov, 11
dec, 12
];
This is the mapping field:
MapSubString('DutchEnglishMonthMapping',Month) as MonthTEST,
MonthTEST |
---|
1 |
2 |
3 |
4 |
5 |
And this is how I am trying to create the datefield, that works jan, feb, march and apr but not may.
Date(floor(Date('1.'& (MapSubString('DutchEnglishMonthMapping',Month)) & '.' & Year, 'DD.MM.YYYY')), 'DD-MM-YYYY') as DateField,'
DateField |
---|
01-01-2016 |
01-02-2016 |
01-03-2016 |
01-04-2016 |
The Month in the data is 'mei' ... that should be mapping to may or 5 ..
Cheers,
Niko
MonthTEST | DateField | Month |
---|---|---|
1 | 01-01-2016 | jan |
2 | 01-02-2016 | feb |
3 | 01-03-2016 | mrt |
4 | 01-04-2016 | apr |
5 | mei |
it shows the number 5 on the left for some reason on the table.. And others on right?
Cheers,
Niko
LOAD * inline [
Abbr, Month, Number
maart, Mar, 3
mrt, Mar, 3
mei, May,5
juni, Jun, 6
juli, Jul, 7
okt, Oct 10
.. ];
like this create the table like above
Maybe you used as expression:
Date(makedate(Year, applymap('DutchEnglishMonthMapping',Month)), 'DD-MM-YYYY') as DateField
and parallel to check how the data will be mapped:
applymap('DutchEnglishMonthMapping',Month, 'not mapped value: ' & Month) as mappingCheck
- Marcus