ApplyMap with Max

[Anonymous]

Hi Guys,

I have table with 3 fields:

ID_number (Klucz_Mandant_Nr_osobowy) Company (JG_skrót) and Date_of_Fire (Data_zwolnienia).

For each Id_number can be a few companies and for each companies can be few Dates.

Example:

600-000002,  Company 1, Date - 2017-09-25

600-000002,  Company 2, Date - 2015-01-02

And now i want to have the result in apply map as :

600-000002, Company 1

Company 1 has date greater then Company2 so the applymap should take the first one (max date)

How can i do this ?

Please help,

Jacek Antek

[Peter_Cammaert]

Use FirstSortedValue() aggregation function. Being an aggregation function, you will need a GROUP BY clause.

If your Dates are real QlikView dates (i.e. dual values), they can be used to numerically order companies. Your Mapping table creation load then becomes somthing similar to:

MapID2Company:

MAPPING

LOAD Klucz_Mandant_Nr_osobowy AS IndexValue,

     FirstSortedValue(JG_skrót, -Data_zwolnienia) AS TranslationValue

RESIDENT Facts

GROUP BY Klucz_Mandant_Nr_osobowy;

Best,

Peter

[Anonymous]

Thank you, but this is not working for me with your code.

The result (Aktualna = TranslationValue) is empty for 600-0000002. There is a lot of Fire_dates and maybe this is a problem?

Image is for code:

Max_Spolki_nazwy_skrot:

load

Klucz_Mandant_Nr_osobowy,

JG_skrót,

Data_zwolnienia,

FirstSortedValue(JG_skrót, -Data_zwolnienia) AS Aktualna

from

$(HrIBSQvd)SAP1_*.qvd (qvd)

group by Klucz_Mandant_Nr_osobowy,JG_skrót,Data_zwolnienia;

[Peter_Cammaert]

Too much data (dates in this case) shouldn't be a problem for QlikView

You will get NULL values from FirstSortedValue() however, if there are multiple possible company values for the same date, as the aggregation function can only return one value and doesn't know which one to take.

What should happen in that case?

[Anonymous]

thank you Peter,

I solved the problem using 2 applymaps:

Max_Spolki_max_data:

mapping load

Klucz_Mandant_Nr_osobowy,

date(max(Data_zwolnienia))

from

$(HrIBSQvd)SAP1_*.qvd (qvd)

group by Klucz_Mandant_Nr_osobowy;

NoConcatenate

Max_Spolki_JG_skrót:

load

Klucz_Mandant_Nr_osobowy,

JG_skrót

from $(HrIBSQvd)SAP1_*.qvd (qvd)

where applymap('Max_Spolki_max_data',Klucz_Mandant_Nr_osobowy,0)=date(Data_zwolnienia);

So here I am creating where clause and check if the max date is equal date value from first applymap

Best Wishes,
Jacek Antek

[Peter_Cammaert]

Don't you get double results for some of your IDs?

I just naticed that you didn't use my example in the first test. In your example code you were adding too many GROUP BY fields. You only need one, Klucz_Mandant_Nr_osobowy.

What you are trying to do in your second example can be done with a single call to FirstSortedValue(). Makes your script much simpler...

[Anonymous]

Hi Peter,

I was using your code:

MapID2Company:

MAPPING

LOAD Klucz_Mandant_Nr_osobowy AS IndexValue,

     FirstSortedValue(JG_skrót, -Data_zwolnienia) AS TranslationValue

RESIDENT Facts

GROUP BY Klucz_Mandant_Nr_osobowy;

And it is no working unfortunately - there is null value for ID_number 600-0000002 and should be Company "JICT".
You wrote:

if there are multiple possible company values for the same date

  unfortunately here I have this issue so FirstSortedValue will not work…

Best wishes,

Jacek Antek

[Peter_Cammaert]

That can be fixed. firstSortedValue() allows for a third parameter that can be used to select from multiple "good" results. But then we need to know what value is better than the others and why.

Peter

[Edit] The Rank parameter cannot be used for that. It just specifies which result to return.

[Anonymous]

Thank you Peter,

it can be a problem here how to differ for example 3 different company names as TEXT ?

Best Wishes,

Jacek Antek

[Peter_Cammaert]

Does this mean that you are ok with any arbitrary selection of the linked company?