First unique distinct values sorted by date

[Sebastian_Dec]

Hi I have a table with 50000 unique records for ArtId, I am trying to get one unique ArtId along with COD-TAU for the latest Year_Month.

Something like this:

ArtId	DOS-COD	COT-COD	Month	Year	Year_Month
10069	1000	2	7	2021	2021.07
10069	1000	4	7	2022	2022.07
10069	1000	42	5	2023	2023.05
10069	1000	40	7	2023	2023.07

The result for this record should be:

10069

1000

40

7

2023

2023.07

I try use FirstValue() and FirstSortedValue() function but I always end up with 37 records (as many as there are unique records for "COT-COD",  instead of e.g. 50000 unique records for ArtId.

Dossie1000_tmp:
Load
    FirstValue(ArtId) as ArtId,
    //FirstSortedValue(ArtId, Year_Month),
    //"DOS-COD",
    "COT-COD",
    Year_Month
    //Month, 
    //Year
Resident DataFakturyTable
Group By Year_Month, "COT-COD"

What I do wrong?

[marcus_sommer · Accepted solution]

Your table-data are more granular as your intended group by - therefore you couldn't reach your results. This means you need at least one step more, maybe with:

t: load ArtId, DOS_COD, COT_COD, YearMonth from Source;
    inner join(t) load ArtId, max(YearMonth) as YearMonth resident t group by ArtId;

I suggest to create YearMonth as number and not as string - in this case here  a string and using maxstring() instead of max() may work but there are surely further measurements where a string would be a disadvantage.

[BrunPierre]

Hi, try this

Dossie1000_tmp:
LOAD ArtId,
[DOS-COD],
[COT-COD],
Month,
Year,
Year_Month,
Date(Date#(Year_Month, 'YYYY.MM'), 'YYYY.MM.DD') as Date /*Convert Year month to date*/
FROM SourceTable;

Inner Join(Dossie1000_tmp)
LOAD Distinct ArtId,
Max(Date) as Date
Resident Dossie1000_tmp
Group By ArtId;

DROP Field Date From Dossie1000_tmp; /*Optional */

[Sebastian_Dec]

Hi @marcus_sommer , I don't know if I understood you correctly, but I don't have any results.

My code:

t: 
load 
    ArtId, 
    "DOS-COD", 
    "COT-COD", 
    YearMonth 
FROM [lib://QlikContainers\Prod\QlikRaporty\2.QVD\Faktury.qvd]
(qvd);

inner join(t) 
load 
	ArtId, 
    max(YearMonth) as YearMonth 
    resident t 
group by ArtId;


Dossie1000_tmp:
Load
    FirstValue(ArtId) as ArtId,
    //FirstSortedValue(ArtId, DataFaktury),
    //"DOS-COD",
    "COT-COD",
    YearMonth
Resident t
Group By YearMonth, "COT-COD"
;

[Sebastian_Dec]

Hi, @BrunPierre , unfortunately, after applying the code, the duplicates still did not disappear.

Dossie1000_tmp:
LOAD 
	ArtId,
    [DOS-COD],
    [COT-COD],
    Month,
    Year,
    YearMonth 
FROM [lib://QlikContainers\Prod\QlikRaporty\2.QVD\Faktury.qvd]
(qvd);


Inner Join(Dossie1000_tmp)
LOAD 
	Distinct ArtId,
	MaxString(YearMonth) as Year_Month
Resident Dossie1000_tmp
Group By ArtId;

[marcus_sommer]

Like described max() will work for numbers and maxstring() for string-values. Further there should be no further loads of the source-qvd and/or against the resident-table - at least not at this time because it may impact the loads itself and/or the way you will look on the results.

[Or]

It looks like you're not quite completing the required grouping and aggregation. It should look something like:

Load ArtId, Max(YearMonth) as Year_Month, FirstSortedValue(SomeField1,YearMonth) as SomeField1, FirstSortedValue(SomeField2,YearMonth) as SomeField2 etc

From YourTable

Group By ArtId

[Sebastian_Dec]

Ok, @marcus_sommer @BrunPierre , I changed my approach to the code and modified it a bit... it meets the assumptions, but it generates errors that I don't understand.

I removed the year and month and instead changed the date to a number so that it would be in the form, e.g. 44600

Num([HVE-DATFAC]) as NumDate,

([HVE-DATFAC] is a date column day, month, year)

My code currently looks like this:

Dossie1000_tmp:
LOAD 
    ArtId,
    [DOS-COD],
    [COT-COD],
    NumDate
Resident Faktury
Where "DOS-COD" = 1000
Order By NumDate Desc;

 Inner Join(Dossie1000_tmp)
 LOAD Distinct ArtId,
 Max(NumDate) as NumDate
 Resident Dossie1000_tmp
 Group By ArtId
 ;

DROP Field NumDate From Dossie1000_tmp;

But there is one big problem, it shows me, for example, this result, while in the original table have code 9 for 2021 and cod 40 for 2022/2023

Current code:

Original:

I try change Max(NumDate) as NumDate to Min(NumDate) as NumDate but it doesn't work

I have Order By NumDate Desc;  but it doesn't work

I don't understand why it still takes the first date and not the last...

[Lech_Miszkiewicz]

Hi @Sebastian_Dec what happens with table "Faktury" you are not dropping it in your script (or we just cannot se it) so it is hard to tell where the issue lies as other tables can impact results of what you see in UI

[marcus_sommer]

There is no error - maybe other results as you expects but no error. In regard to the aggregation-load the order by has no impact for min/max.

Beside this I think you don't look on the result of your filter-query else to other data within your data-model which is confusing you. Therefore follow my suggestion from above and load only the raw-data for the filter-query and removing afterwards all other tables unless the filter-table.