topic Re: Making two Applymap function in one syntax in QlikView

Making two Applymap function in one syntax

Nocto — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Making two Applymap function in one syntax

Kushal_Chawda — Thu, 29 Oct 2020 17:17:55 GMT

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

Re: Making two Applymap function in one syntax

Nocto — Thu, 29 Oct 2020 17:24:46 GMT

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.

Re: Making two Applymap function in one syntax

Martijn_W — Thu, 29 Oct 2020 19:11:11 GMT

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/InterRecordFunctions/Previous.htm#:~:text=Previous()%20finds%20the%20value,the%20function%20will%20return%20NULL%20.&text=The%20expression%20or%20field%20containing%20the%20data%20to%20be%20measured.

you would use the Previous funcion like so:

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

Re: Making two Applymap function in one syntax

Kushal_Chawda — Thu, 29 Oct 2020 22:34:45 GMT

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

Re: Making two Applymap function in one syntax

Martijn_W — Fri, 30 Oct 2020 10:35:34 GMT

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;