Making two Applymap function in one syntax

[Nocto]

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 

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
        Key &'|'& Monat,
        Key &'|'& (Monat-1)
        Resident Stammdaten
        ;  

[Map_AktuellerMonat_AZK100]:
  mapping
        load
       Key &'|'& Monat,
        AZK
Resident Stammdaten
        ;

        NoConcatenate
        temp_MA_Infos:
        load
        *,
        applymap('Map_AktuellerMonat_AZK100',applymap('Map_Vormonat_primary',Key &'|'& Monat,0),0) as [AZK 100 % Vormonat]

        resident Stammdaten
        ;

        drop table Stammdaten;
        rename table temp_MA_Infos to Stammdanten;

It wont Work.

can you guys help met out.

[Kushal_Chawda]

@Nocto  what you are trying achieve? Can you please elaborate?

[Nocto]

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.

[Martijn_W · Accepted solution]

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

[Kushal_Chawda]

@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;

[Martijn_W]

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;