Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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;
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?
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
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...
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
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.
Thank you Peter,
it can be a problem here how to differ for example 3 different company names as TEXT ?
Best Wishes,
Jacek Antek
Does this mean that you are ok with any arbitrary selection of the linked company?