topic Re: Date(max does not working after joining multiple tables via wildcard load in App Development

Date(max does not working after joining multiple tables via wildcard load

StefanKS — Thu, 14 Jul 2022 07:49:46 GMT

Hi,

maybe someone can help me with the following problem.

I want to join 2 different tables (each table via wildcard load to get different date records) :

Table1:

LOAD

Date(DatumBilanz) as Datum_Bilanz,

KontoNr,

Bilanzwert

FROM [lib://RW (hypoooe_lhypkss)/QLIK_UGB-Bilanz_Daten_UGB_*.xlsx] (ooxml, embedded labels, table is Sheet1)

Table2:

LOAD

Date(Date#(Right(FileBaseName(),6),'YYMMDD'),'DD.MM.YYYY') as Datum_GN,

KontoNr,

GN-Wert

FROM [lib://RW (hypoooe_lhypkss)/GN Abgleich BB mit Erfolgskontoübersicht_*.xlsx]
(ooxml, embedded labels, table is [UGB aktiv])

After that, I want to compare the value of Bilanzwert of the 2 tables based on the latest date results in SET ANALYIS: Therefore I used the following date(max for each value -->

Sum({<Datum_Bilanz={'$(=Date(max(Datum_Bilanz)))'}>} Bilanzwert)

Sum({<Datum_GN={'$(=Date(max(Datum_GN)))'}>} GN-Wert)

Unfortunately I get duplicates as shown below. It seems, that Date(max does not work:

Many thanks in advance for your support!

Re: Date(max does not working after joining multiple tables via wildcard load

vinieme12 — Thu, 14 Jul 2022 09:23:50 GMT

those are not duplicates, those are the records as in Table1 and Table2,

This is being calculated for each datum_bilanz and datum_gn that exists since you've added them as Dimensions in the Table chart

Also Max() in set will return the Max(datum_bilanz ) for the entire dataset and not Max(datum_bilanz ) for each KontoNR

if you want to see Max(Max(datum_bilanz ) for each KontoNR use a calculated dimension as below

Dimensions

=KontoNR

=aggr(NODISTINCT if(datum_bilanz=Max(datum_bilanz ) ,datum_bilanz),KontoNR) << uncheck IncludeNullValues for this dimension

=aggr(NODISTINCT if(datum_gn =Max(datum_gn ) ,datum_gn ),KontoNR) << uncheck IncludeNullValues for this dimension

Measures

=sum(Bilanzwert)

=sum(GN-Wert)

Re: Date(max does not working after joining multiple tables via wildcard load

StefanKS — Thu, 14 Jul 2022 13:39:34 GMT

Thanks a lot! That works