topic Re: ApplyMap with Max in QlikView

ApplyMap with Max

Anonymous — Thu, 12 Oct 2017 08:51:24 GMT

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

Re: ApplyMap with Max

Peter_Cammaert — Thu, 12 Oct 2017 09:10:20 GMT

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

Re: ApplyMap with Max

Anonymous — Thu, 12 Oct 2017 09:54:44 GMT

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;

Re: ApplyMap with Max

Peter_Cammaert — Thu, 12 Oct 2017 10:28:06 GMT

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?

Re: ApplyMap with Max

Anonymous — Thu, 12 Oct 2017 10:32:27 GMT

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

Re: ApplyMap with Max

Peter_Cammaert — Thu, 12 Oct 2017 10:40:02 GMT

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

Re: ApplyMap with Max

Anonymous — Thu, 12 Oct 2017 10:52:37 GMT

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

Re: ApplyMap with Max

Peter_Cammaert — Thu, 12 Oct 2017 11:00:13 GMT

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.

Re: ApplyMap with Max

Anonymous — Thu, 12 Oct 2017 11:06:12 GMT

Thank you Peter,

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

Best Wishes,

Jacek Antek

Re: ApplyMap with Max

Peter_Cammaert — Thu, 12 Oct 2017 11:14:54 GMT

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

Re: ApplyMap with Max

Anonymous — Thu, 12 Oct 2017 11:27:58 GMT

I have checked this and my row source table looks like:

I have to take only last row from this table with max date.

We should not have more than 1 company for each date in my model.

It seems that FirstSortedValue should work here.

Best Wishes,

Jacek

Re: ApplyMap with Max

daveamz — Thu, 12 Oct 2017 11:45:58 GMT

Hi Jacek,

If there are no nulls in Data_zwolnienia you can use:

_temp:

LOAD Klucz_Mandant_Nr_osobowy,

AutoNumber(RowNo(), Klucz_Mandant_Nr_osobowy) AS Sort_ID,

JG_skrót,

Data_zwolnienia

RESIDENT Facts

ORDER BY Klucz_Mandant_Nr_osobowy, Data_zwolnienia DESC;

MapID2Company:

MAPPING

LOAD Klucz_Mandant_Nr_osobowy AS IndexValue,

JG_skrót AS TranslationValue

RESIDENT _temp

WHERE Sort_ID = 1;

DROP TABLE _temp;

Regards,

David