Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Nocto
Contributor III
Contributor III

Making two Applymap function in one syntax

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 before2020-10-29 17_56_40-QlikView x64 - [F__impex_QlikView_00_ZentralsteuerungQlikView_ - __Remote.png 

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

Accepted Solutions
Martijn_W
Contributor III
Contributor III

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

View solution in original post

5 Replies
Kushal_Chawda

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

Nocto
Contributor III
Contributor III
Author

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
Contributor III
Contributor III

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

View solution in original post

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
Contributor III
Contributor III

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;