topic Re: finding min date in QlikView

finding min date

Mon, 28 Sep 2015 13:38:57 GMT

Hello All,

i have a set of data( Ticket and date);

example data set:

ticket date

100 9/10/2015

100 10/10/2015

100 11/10/2015

101 10/10/2015

so i want to get the min date associated to every ticket ie:

ticket date

100 9/10/2015

101 10/10/2015

could you please help me get the same.

Regards

Mythili

Re: finding min date

avinashelite — Mon, 28 Sep 2015 13:41:05 GMT

try like this

LOAD ticket,

min(date) as Date

group by ticket

from table ;

Re: finding min date

Digvijay_Singh — Mon, 28 Sep 2015 13:42:46 GMT

in front-end normally below function is used -

firstsortedvalue(ticket,date)

In script it could be -

Load ticket,min(date) resident table group by ticket

Re: finding min date

avinashelite — Mon, 28 Sep 2015 13:44:54 GMT

Temp:

LOAD * Inline

[

ticket,date

100,9/10/2015

100,10/10/2015

100,11/10/2015

101,10/10/2015

];

LOAD ticket,

Date(min(date),'DD-MM-YYYY') as D

Resident

Temp

Group by

ticket;

Drop Table Temp;

Re: finding min date

Mon, 28 Sep 2015 13:46:47 GMT

Hi Mythili,

Try this

Ticket:

LOAD * Inline

[ticket, date

100, 9/10/2015

100, 10/10/2015

100, 11/10/2015

101, 10/10/2015];

Mindate:

LOAD ticket as TICKET, date(Min(date)) as MIN_DATE Resident Ticket group by ticket;

Drop table Ticket;

Regards

kalyan

Re: finding min date

Digvijay_Singh — Mon, 28 Sep 2015 14:01:53 GMT

In front-end like this,

Dimension - ticket

exp - min(date)

ensure date is already converted to date type using date and date# functions.

Re: finding min date

Tue, 29 Sep 2015 10:01:59 GMT

Thanks Avanish,but this is giving me the min date of the entire list and not according to the ticket.

Re: finding min date

Tue, 29 Sep 2015 10:02:55 GMT

Thanks... but this is giving me the min of the entire list and not according to the ticket.

Re: finding min date

avinashelite — Tue, 29 Sep 2015 10:17:59 GMT

did you tried this in script ?

Temp:

LOAD * Inline

[

ticket,date

100,9/10/2015

100,10/10/2015

100,11/10/2015

101,10/10/2015

];

LOAD ticket,

Date(min(date),'DD-MM-YYYY') as D

Resident

Temp

Group by

ticket;

Drop Table Temp;

if your trying the same in Front end try like this:

=aggr(min(date),ticket) this should work

Re: finding min date

qlikviewwizard — Tue, 29 Sep 2015 10:44:54 GMT

Hi Mythili,

Please check the attached file and solution.

Data_Temp:

LOAD * Inline

[

ticket,date

100,9/10/2015

100,10/10/2015

100,11/10/2015

101,10/10/2015

];

Data:

LOAD ticket,

Date(min(date),'MM-DD-YYYY') as min_date

Resident Data_Temp

Group by

ticket;

Re: finding min date

sasiparupudi1 — Tue, 29 Sep 2015 11:59:47 GMT

First convert your date string into a qv Date

T1:

LOAD ticket,Date(Date#(date,'DD/MM/YYYY'),'DD/MM/YYYY') as date Inline

[

ticket,date

100,9/10/2015

100,10/10/2015

100,11/10/2015

101,10/10/2015

];

in the front end

create a straight table

use ticket as dimension

use min(dates) as expression

hth

Sasi

Re: finding min date

Tue, 29 Sep 2015 13:58:45 GMT

Hello All,

thanks for the help so far, but just to make things clear: the script that i ma using is:

CompletedTemp:

LOAD Distinct

TICKET ,

Date( Makedate(Right(DATE,4),Mid(DATE,4,2),Left(DATE,2)),'DD-MM-YYYY') as DATE1

FROM

$(vQVD)\DAILY_TICKETS_RUNNING.qvd (qvd)

Where STATUS='Completed'

;

STABILITY_TMP:

NOCONCATENATE

LOAD

Distinct

TICKET ,

DATE(MIN(DATE1)) AS date2

RESIDENT CompletedTemp

WHERE TICKET=TICKET

Group by TICKET;

EXIT SCRIPT;

I will then have to join to the table "STABILITY_TMP" the rest of the fields based on the unique combinations of "ticket" and "Date" fields.

Thanks to suggest.

Mythili

Re: finding min date

qlikviewwizard — Tue, 29 Sep 2015 14:24:36 GMT

Hi,

Did you try as I have given the script? It is working for me.

If possible upload the sample file.