topic Re: Maxstring or something else for max-date in script or visualation in QlikView

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

Anonymous — Thu, 29 Sep 2016 13:43:09 GMT

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!

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

Gysbert_Wassenaar — Thu, 29 Sep 2016 14:06:11 GMT

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
      ;

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

Anonymous — Thu, 29 Sep 2016 14:26:28 GMT

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!!

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

vishsaggi — Thu, 29 Sep 2016 14:48:39 GMT

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);

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

Anonymous — Mon, 14 Nov 2016 13:55:29 GMT

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!

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

Anonymous — Mon, 14 Nov 2016 13:56:10 GMT

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!