
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
ApplyMap with Max
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
- « Previous Replies
-
- 1
- 2
- Next Replies »

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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...

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you Peter,
it can be a problem here how to differ for example 3 different company names as TEXT ?
Best Wishes,
Jacek Antek

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Does this mean that you are ok with any arbitrary selection of the linked company?

- « Previous Replies
-
- 1
- 2
- Next Replies »