Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Maxstring or something else for max-date in script or visualation

Hi all,

I put my table in the appendix.

Now I want to show the max  "Anzahl" for each day (last timestamp for this day) in a chart - seperated into "Teilnehmer A" and "Teilnehmer B".

Is it possbile to do this in visualtion or how to solve it in the script.

Thank you for your help!

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

Hi Robert,

On Scrit calculate the date:

LOAD

    Teilnehmer,

    Anzahl,

    Timestamp(Timestamp#(Zeitstempel,'DD.MM.YYY hh:mm'),'DD/MM/YYYY hh:mm') as Zeitstempel,

    Date(Floor(Timestamp(Timestamp#(Zeitstempel,'DD.MM.YYY hh:mm'),'DD/MM/YYYY hh:mm'))) as Date

FROM ...

On Front End you can calculate max value an last value by all dims you want.

For max value: =Max(Anzahl)

For last value: =FirstSortedValue(Anzahl,-Zeitstempel,1)

Regards!!

View solution in original post

5 Replies
Gysbert_Wassenaar

Perhaps like this:

Data:

LOAD Teilnehmer,

    Anzahl,

    Timestamp#(Zeitstempel,'DD.MM.YYYY hh:mm') as Zeitstempel,

    Date(Floor(Timestamp#(Zeitstempel,'DD.MM.YYYY hh:mm')),'DD.MM.YYYY') as Tag

FROM

[c234704.csv]

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

Summary:

LOAD

  Teilnehmer,

  Tag,

  Timestamp(Max(Zeitstempel),'DD.MM.YYYY hh:mm') as MaxZeitstempel,

  FirstSortedValue(Anzahl, -Zeitstempel) as Anzahl_Max_Zeitstempel

RESIDENT

  Data

GROUP BY

  Teilnehmer,

  Tag

      ;


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

Hi Robert,

On Scrit calculate the date:

LOAD

    Teilnehmer,

    Anzahl,

    Timestamp(Timestamp#(Zeitstempel,'DD.MM.YYY hh:mm'),'DD/MM/YYYY hh:mm') as Zeitstempel,

    Date(Floor(Timestamp(Timestamp#(Zeitstempel,'DD.MM.YYY hh:mm'),'DD/MM/YYYY hh:mm'))) as Date

FROM ...

On Front End you can calculate max value an last value by all dims you want.

For max value: =Max(Anzahl)

For last value: =FirstSortedValue(Anzahl,-Zeitstempel,1)

Regards!!

vishsaggi
Champion III
Champion III

Hi Robert,

Your CSV file needs to be transformed before you can use any expressions. See below and use Manuel or Gysbert functions for Max values shown by days.

LOAD Left(Trim(SampleField), 13) AS Teilnehmer,

     Replace(Replace(Trim(SampleField), Right(Trim(SampleField),16), ''), Left(Trim(SampleField), 13), '') AS Anzhal,

     Right(Trim(SampleField),16) AS Zeitstempel;

    

LOAD [Teilnehmer Anzahl Zeitstempel] AS SampleField

FROM

[..\Desktop\TestCSV.csv]

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

Anonymous
Not applicable
Author

Hi all,

now I have two more Options:

TeilnehmerA

TeilnehmerB

TeilnehmerC

TeilnehmerD

But the solutions dosen't work anymore:

For last value: =FirstSortedValue(Anzahl,-Zeitstempel,1)


Can u help me again?


Thank you!

Anonymous
Not applicable
Author

Hello Manuel,

now I have two more Options:

TeilnehmerA

TeilnehmerB

TeilnehmerC

TeilnehmerD

But the solutions dosen't work anymore:

For last value: =FirstSortedValue(Anzahl,-Zeitstempel,1)


Can u help me again?


Thank you!