Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

How to load the Min date

Hi,

I have a table :

Date | Call No | status

i want to load the min(date) of a perticular call no where status is 79 or 13...

Can any one help me to write the code for this

i tried this but it is not working

 

LOAD

min(Date) as MDate,

Call_no,

  Status

FROM (ooxml, embedded labels, table is Day1) where status=79 or status=13;

1 Solution

Accepted Solutions

Re: How to load the Min date

Hi

Try like this

load min(Date) AS minDate,

Call_no,

Status

from filename.xls where match(Status,13,79) group by Call_no,Status;

Or you can try like this

Table1:

Load Call_no,Status,Date from filename.xls where match(Status,13,79);

Temp:

Inner join(Table1)

Load min(Date) as Date, Call_no resident Table1 group by Call_no;

Note:

Whenever u use aggregation please use group by clause..

From ur post i note down, after tempDate:

i think u forget to add load statement..please correct it that..

N one more thing..u must add all field in group by clause except using in aggregation function

Ex:

Load min(date),id,callno from tablename group by id,callno;

Hope it helps

10 Replies
Not applicable

Re: How to load the Min date

Hi grace,

try this:

Table1:

LOAD

Date,

Call_no,

Status

FROM (ooxml, embedded labels, table is Day1) where status=79 or status=13;

TempMinDate:

min(Date) as MinDate

Resident Table1;

Hope this can help you.

Regards,

Heri

Not applicable

Re: How to load the Min date

For every callno you can use group by, with this code:

Table1:

LOAD

Date,

Call_no,

Status

FROM (ooxml, embedded labels, table is Day1) where status=79 or status=13;

TempMinDate:

Call_no,

Statusas temp_status,

min(Date) as MinDate

Resident Table1

Group By Call_no;

Not applicable

Re: How to load the Min date

It is giving error as

"Unknown statement"

TempMinDate:

min(Date) as MinDate

Resident Table1;

Not applicable

Re: How to load the Min date

Did you have tried the second code that I provided using group by?

Regards,

Heri

Regards,

Heri

Business Intelligence Developer

PT. Evotech Distribusi

Ruko Garden Shopping Arcade Blok.C/09/AS

Jl. Podomoro Avenue, Podomoro City - Jakarta Barat

Phone: +62 21 569 823 85 / 86

Fax: +62 21 569 823 87

Mobile: +62 813 6463 7227

Email: heri@evotech.co.id

Not applicable

Re: How to load the Min date

same error i am getting for both the code...

Not applicable

Re: How to load the Min date

Can you copy paste your code? I want to see did it missed something or not. I tried in my computer, and it worked fine.

Thanks.

Regards,

Heri

Business Intelligence Developer

PT. Evotech Distribusi

Ruko Garden Shopping Arcade Blok.C/09/AS

Jl. Podomoro Avenue, Podomoro City - Jakarta Barat

Phone: +62 21 569 823 85 / 86

Fax: +62 21 569 823 87

Mobile: +62 813 6463 7227

Email: heri@evotech.co.id

Not applicable

Re: How to load the Min date

 

Table1:
LOAD Date,
call_no,
Status
FROM (ooxml, embedded labels, table is Sheet1) where Status=79 or Status=13;

tempdate:
call_no,
status as temp_status,
min(Date) as MinDate
Resident Table1
group by call_no;

Not applicable

Re: How to load the Min date

Try change 'status as temp_status' by 'Status as temp_status'..

Regards,

Heri

Business Intelligence Developer

PT. Evotech Distribusi

Ruko Garden Shopping Arcade Blok.C/09/AS

Jl. Podomoro Avenue, Podomoro City - Jakarta Barat

Phone: +62 21 569 823 85 / 86

Fax: +62 21 569 823 87

Mobile: +62 813 6463 7227

Email: heri@evotech.co.id

Re: How to load the Min date

Hi

Try like this

load min(Date) AS minDate,

Call_no,

Status

from filename.xls where match(Status,13,79) group by Call_no,Status;

Or you can try like this

Table1:

Load Call_no,Status,Date from filename.xls where match(Status,13,79);

Temp:

Inner join(Table1)

Load min(Date) as Date, Call_no resident Table1 group by Call_no;

Note:

Whenever u use aggregation please use group by clause..

From ur post i note down, after tempDate:

i think u forget to add load statement..please correct it that..

N one more thing..u must add all field in group by clause except using in aggregation function

Ex:

Load min(date),id,callno from tablename group by id,callno;

Hope it helps