How to load the Min date

[#1]

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;

[#2]

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

[#3]

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;

[#4]

It is giving error as

"Unknown statement"

TempMinDate:

min(Date) as MinDate

Resident Table1;

[#5]

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

[#6]

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

[#7]

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

[#8]

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;

[#9]

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

[MayilVahanan · Accepted solution]

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