Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey guys I hopefully you can help me:
I Need the AZK 100% Field as a double applymatch syntax not as a Peek().
Monat=Month VorMonat =One Month before
It's certainly possible, however you have to really be mindful of the key you're trying to produce in the mapping table. I got it working like this:
Stammdaten:
LOAD * INLINE [
Key, Monat, Name, PersonalNR, AZK
Mark_Bremen_Willhelm_karmann_2020_01, 1, Mark, 1, 40
Mark_Bremen_Willhelm_karmann_2020_02, 2, Mark, 1, 40
Mark_Bremen_Willhelm_karmann_2020_03, 3, Mark, 1, 42
Mark_Bremen_Willhelm_karmann_2020_04, 4, Mark, 1, 36
Mark_Bremen_Willhelm_karmann_2020_05, 5, Mark, 1, 35
Paul_Bremen_Willhelm_karmann_2020_01, 1, Paul, 2, 40
Paul_Bremen_Willhelm_karmann_2020_02, 2, Paul, 2, 36
Paul_Bremen_Willhelm_karmann_2020_03, 3, Paul, 2, 35
Paul_Bremen_Willhelm_karmann_2020_04, 4, Paul, 2, 45
Paul_Bremen_Willhelm_karmann_2020_05, 5, Paul, 2, 20
Karin_Bremen_Willhelm_karmann_2020_01, 1, Karin, 3, 40
Karin_Bremen_Willhelm_karmann_2020_02, 2, Karin, 3, 45
Karin_Bremen_Willhelm_karmann_2020_03, 3, Karin, 3, 42
Karin_Bremen_Willhelm_karmann_2020_04, 4, Karin, 3, 43
Karin_Bremen_Willhelm_karmann_2020_05, 5, Karin, 3, 38
];
[Map_Vormonat_primary]:
mapping
load
if(right(Key,2)='01',left(Key,LEN(Key)-2)&'02',
if(right(Key,2)='02',left(Key,LEN(Key)-2)&'03',
if(right(Key,2)='03',left(Key,LEN(Key)-2)&'04',
if(right(Key,2)='04',left(Key,LEN(Key)-2)&'05',
if(right(Key,2)='05',left(Key,LEN(Key)-2)&'06',
if(right(Key,2)='06',left(Key,LEN(Key)-2)&'07',
if(right(Key,2)='07',left(Key,LEN(Key)-2)&'08',
if(right(Key,2)='08',left(Key,LEN(Key)-2)&'09',
if(right(Key,2)='09',left(Key,LEN(Key)-2)&'10',
if(right(Key,2)='10',left(Key,LEN(Key)-2)&'11',
if(right(Key,2)='11',left(Key,LEN(Key)-2)&'12',
if(right(Key,2)='12',left(Key,LEN(Key)-5)&((mid(Key,LEN(Key)-4,2))+1)&'_01'
)))))))))))) AS KeyVormonat,
AZK AS AZK_Temp
Resident Stammdaten
;
temp_MA_Infos:
NoConcatenate load *
,applymap('Map_Vormonat_primary',Key,0) as [AZK 100 % Vormonat]
resident Stammdaten
;
drop table Stammdaten;
rename table temp_MA_Infos to Stammdanten;
Because you already had the monthnumber included in the key, you have to do some text magic:)
This will also work across years. For this situation you really don't need more than one mapping table.
Other valid alternatives would be the previous() function and a join.
see this article on the previous function https://help.qlik.com/en-US/sense/September2020/Subsystems/Hub/Content/Sense_Hub/Scripting/InterReco....
you would use the Previous funcion like so:
Stammdaten:
LOAD * INLINE [
Key, Monat, Name, PersonalNR, AZK
Mark_Bremen_Willhelm_karmann_2020_01, 1, Mark, 1, 40
Mark_Bremen_Willhelm_karmann_2020_02, 2, Mark, 1, 40
Mark_Bremen_Willhelm_karmann_2020_03, 3, Mark, 1, 42
Mark_Bremen_Willhelm_karmann_2020_04, 4, Mark, 1, 36
Mark_Bremen_Willhelm_karmann_2020_05, 5, Mark, 1, 35
Paul_Bremen_Willhelm_karmann_2020_01, 1, Paul, 2, 40
Paul_Bremen_Willhelm_karmann_2020_02, 2, Paul, 2, 36
Paul_Bremen_Willhelm_karmann_2020_03, 3, Paul, 2, 35
Paul_Bremen_Willhelm_karmann_2020_04, 4, Paul, 2, 45
Paul_Bremen_Willhelm_karmann_2020_05, 5, Paul, 2, 20
Karin_Bremen_Willhelm_karmann_2020_01, 1, Karin, 3, 40
Karin_Bremen_Willhelm_karmann_2020_02, 2, Karin, 3, 45
Karin_Bremen_Willhelm_karmann_2020_03, 3, Karin, 3, 42
Karin_Bremen_Willhelm_karmann_2020_04, 4, Karin, 3, 43
Karin_Bremen_Willhelm_karmann_2020_05, 5, Karin, 3, 38
];
temp_MA_Infos2:
NoConcatenate load *
,if(previous(Name)=Name,previous(AZK),0) as [AZK 100 % Vormonat]
resident Stammdaten
order by Key asc;
drop table Stammdaten;
rename table temp_MA_Infos2 to Stammdaten;
Instead of using Applymap, you could simply join the mapping table onto the original table, like so:
left join (Stammdaten)
load
if(right(Key,2)='01',left(Key,LEN(Key)-2)&'02',
if(right(Key,2)='02',left(Key,LEN(Key)-2)&'03',
if(right(Key,2)='03',left(Key,LEN(Key)-2)&'04',
if(right(Key,2)='04',left(Key,LEN(Key)-2)&'05',
if(right(Key,2)='05',left(Key,LEN(Key)-2)&'06',
if(right(Key,2)='06',left(Key,LEN(Key)-2)&'07',
if(right(Key,2)='07',left(Key,LEN(Key)-2)&'08',
if(right(Key,2)='08',left(Key,LEN(Key)-2)&'09',
if(right(Key,2)='09',left(Key,LEN(Key)-2)&'10',
if(right(Key,2)='10',left(Key,LEN(Key)-2)&'11',
if(right(Key,2)='11',left(Key,LEN(Key)-2)&'12',
if(right(Key,2)='12',left(Key,LEN(Key)-5)&((mid(Key,LEN(Key)-4,2))+1)&'_01'
)))))))))))) AS Key,
AZK AS [AZK 100 % Vormonat]
Resident Stammdaten
@Nocto what you are trying achieve? Can you please elaborate?
I want like this in the Picture that the month and the Month before are in two different Fields
So AZK actual Month and AZK 100% is the Month before.
It's certainly possible, however you have to really be mindful of the key you're trying to produce in the mapping table. I got it working like this:
Stammdaten:
LOAD * INLINE [
Key, Monat, Name, PersonalNR, AZK
Mark_Bremen_Willhelm_karmann_2020_01, 1, Mark, 1, 40
Mark_Bremen_Willhelm_karmann_2020_02, 2, Mark, 1, 40
Mark_Bremen_Willhelm_karmann_2020_03, 3, Mark, 1, 42
Mark_Bremen_Willhelm_karmann_2020_04, 4, Mark, 1, 36
Mark_Bremen_Willhelm_karmann_2020_05, 5, Mark, 1, 35
Paul_Bremen_Willhelm_karmann_2020_01, 1, Paul, 2, 40
Paul_Bremen_Willhelm_karmann_2020_02, 2, Paul, 2, 36
Paul_Bremen_Willhelm_karmann_2020_03, 3, Paul, 2, 35
Paul_Bremen_Willhelm_karmann_2020_04, 4, Paul, 2, 45
Paul_Bremen_Willhelm_karmann_2020_05, 5, Paul, 2, 20
Karin_Bremen_Willhelm_karmann_2020_01, 1, Karin, 3, 40
Karin_Bremen_Willhelm_karmann_2020_02, 2, Karin, 3, 45
Karin_Bremen_Willhelm_karmann_2020_03, 3, Karin, 3, 42
Karin_Bremen_Willhelm_karmann_2020_04, 4, Karin, 3, 43
Karin_Bremen_Willhelm_karmann_2020_05, 5, Karin, 3, 38
];
[Map_Vormonat_primary]:
mapping
load
if(right(Key,2)='01',left(Key,LEN(Key)-2)&'02',
if(right(Key,2)='02',left(Key,LEN(Key)-2)&'03',
if(right(Key,2)='03',left(Key,LEN(Key)-2)&'04',
if(right(Key,2)='04',left(Key,LEN(Key)-2)&'05',
if(right(Key,2)='05',left(Key,LEN(Key)-2)&'06',
if(right(Key,2)='06',left(Key,LEN(Key)-2)&'07',
if(right(Key,2)='07',left(Key,LEN(Key)-2)&'08',
if(right(Key,2)='08',left(Key,LEN(Key)-2)&'09',
if(right(Key,2)='09',left(Key,LEN(Key)-2)&'10',
if(right(Key,2)='10',left(Key,LEN(Key)-2)&'11',
if(right(Key,2)='11',left(Key,LEN(Key)-2)&'12',
if(right(Key,2)='12',left(Key,LEN(Key)-5)&((mid(Key,LEN(Key)-4,2))+1)&'_01'
)))))))))))) AS KeyVormonat,
AZK AS AZK_Temp
Resident Stammdaten
;
temp_MA_Infos:
NoConcatenate load *
,applymap('Map_Vormonat_primary',Key,0) as [AZK 100 % Vormonat]
resident Stammdaten
;
drop table Stammdaten;
rename table temp_MA_Infos to Stammdanten;
Because you already had the monthnumber included in the key, you have to do some text magic:)
This will also work across years. For this situation you really don't need more than one mapping table.
Other valid alternatives would be the previous() function and a join.
see this article on the previous function https://help.qlik.com/en-US/sense/September2020/Subsystems/Hub/Content/Sense_Hub/Scripting/InterReco....
you would use the Previous funcion like so:
Stammdaten:
LOAD * INLINE [
Key, Monat, Name, PersonalNR, AZK
Mark_Bremen_Willhelm_karmann_2020_01, 1, Mark, 1, 40
Mark_Bremen_Willhelm_karmann_2020_02, 2, Mark, 1, 40
Mark_Bremen_Willhelm_karmann_2020_03, 3, Mark, 1, 42
Mark_Bremen_Willhelm_karmann_2020_04, 4, Mark, 1, 36
Mark_Bremen_Willhelm_karmann_2020_05, 5, Mark, 1, 35
Paul_Bremen_Willhelm_karmann_2020_01, 1, Paul, 2, 40
Paul_Bremen_Willhelm_karmann_2020_02, 2, Paul, 2, 36
Paul_Bremen_Willhelm_karmann_2020_03, 3, Paul, 2, 35
Paul_Bremen_Willhelm_karmann_2020_04, 4, Paul, 2, 45
Paul_Bremen_Willhelm_karmann_2020_05, 5, Paul, 2, 20
Karin_Bremen_Willhelm_karmann_2020_01, 1, Karin, 3, 40
Karin_Bremen_Willhelm_karmann_2020_02, 2, Karin, 3, 45
Karin_Bremen_Willhelm_karmann_2020_03, 3, Karin, 3, 42
Karin_Bremen_Willhelm_karmann_2020_04, 4, Karin, 3, 43
Karin_Bremen_Willhelm_karmann_2020_05, 5, Karin, 3, 38
];
temp_MA_Infos2:
NoConcatenate load *
,if(previous(Name)=Name,previous(AZK),0) as [AZK 100 % Vormonat]
resident Stammdaten
order by Key asc;
drop table Stammdaten;
rename table temp_MA_Infos2 to Stammdaten;
Instead of using Applymap, you could simply join the mapping table onto the original table, like so:
left join (Stammdaten)
load
if(right(Key,2)='01',left(Key,LEN(Key)-2)&'02',
if(right(Key,2)='02',left(Key,LEN(Key)-2)&'03',
if(right(Key,2)='03',left(Key,LEN(Key)-2)&'04',
if(right(Key,2)='04',left(Key,LEN(Key)-2)&'05',
if(right(Key,2)='05',left(Key,LEN(Key)-2)&'06',
if(right(Key,2)='06',left(Key,LEN(Key)-2)&'07',
if(right(Key,2)='07',left(Key,LEN(Key)-2)&'08',
if(right(Key,2)='08',left(Key,LEN(Key)-2)&'09',
if(right(Key,2)='09',left(Key,LEN(Key)-2)&'10',
if(right(Key,2)='10',left(Key,LEN(Key)-2)&'11',
if(right(Key,2)='11',left(Key,LEN(Key)-2)&'12',
if(right(Key,2)='12',left(Key,LEN(Key)-5)&((mid(Key,LEN(Key)-4,2))+1)&'_01'
)))))))))))) AS Key,
AZK AS [AZK 100 % Vormonat]
Resident Stammdaten
@Nocto may be this
Stammdaten:
LOAD * INLINE [
KEY, Monat, Name, PersonalNR, AZK
Mark_Bremen_Willhelm_karmann_2020_01, 1, Mark, 1, 40
Mark_Bremen_Willhelm_karmann_2020_02, 2, Mark, 1, 40
Mark_Bremen_Willhelm_karmann_2020_03, 3, Mark, 1, 42
Mark_Bremen_Willhelm_karmann_2020_04, 4, Mark, 1, 36
Mark_Bremen_Willhelm_karmann_2020_05, 5, Mark, 1, 35
Paul_Bremen_Willhelm_karmann_2020_01, 1, Paul, 2, 40
Paul_Bremen_Willhelm_karmann_2020_02, 2, Paul, 2, 36
Paul_Bremen_Willhelm_karmann_2020_03, 3, Paul, 2, 35
Paul_Bremen_Willhelm_karmann_2020_04, 4, Paul, 2, 45
Paul_Bremen_Willhelm_karmann_2020_05, 5, Paul, 2, 20
Karin_Bremen_Willhelm_karmann_2020_01, 1, Karin, 3, 40
Karin_Bremen_Willhelm_karmann_2020_02, 2, Karin, 3, 45
Karin_Bremen_Willhelm_karmann_2020_03, 3, Karin, 3, 42
Karin_Bremen_Willhelm_karmann_2020_04, 4, Karin, 3, 43
Karin_Bremen_Willhelm_karmann_2020_05, 5, Karin, 3, 38
];
Final:
LOAD *,
if(Monat=1,0,Previous(AZK)) as [AZK 100 % Vormonat]
Resident Stammdaten
Order by KEY,Monat;
DROP Table Stammdaten;
Exactly.
Although, looking at it, that way you can't use it across years and you would have possible contamination if Paul only worked in February for instance. I think you have to use a qualifier to make sure the previous row pertains to the same person.
Out of all the options, Previous() is the best one, though.
Ideally, the data should have a unique identifier for every unique person, that way you can be 100% sure there won't be any weirdness when somebody with the same name shows up in the Data.
You could then use previous like this:
NoConcatenate load *
,if(previous(<UniqueIdentifier>)=<UniqueIdentifier>,previous(AZK),0) as [AZK 100 % Vormonat]
resident Stammdaten
order by KEY, Monat;