Skip to main content

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
StefanKS
Contributor
Contributor

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

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:

Steve24_0-1657784484616.png

 

Many thanks in advance for your support!

 

 

Labels (1)
1 Solution

Accepted Solutions
vinieme12
Champion III
Champion III

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)

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

View solution in original post

2 Replies
vinieme12
Champion III
Champion III

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)

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
StefanKS
Contributor
Contributor
Author

Thanks a lot! That works