Where clause and group by in single table

[Anonymous]

Hi Qlicker,

How to I write the code in the script for Where Clause and Group by  for single table...

i'm writing the script like

Load

EmpId ,

SeqNo

From

Table location;

From this I want to get the max seqNo

Thanks in advance..

[anbu1984]

Load

Col1,

Col2,

Max(Col3)

From

Table location

Where Condition

Group by

Col1,

Col2;

[senpradip007]

Look the below code, hope it will help.

TABLE2:

NoConcatenate

LOAD

Contract_Number,

Transaction_Code,

Date(Max(NewDate),'DD/MM/YYYY') AS I_Date

Resident TABLE1

WHERE Match(Transaction_Code,'T668','TJE6')

Group By Contract_Number, Transaction_Code;

[amit_saini]

For Example:

LOAD doc_id,

max(stamp_date) as Date_Envoi_Facture

Resident ACTION_LOG

where match(log_index,'1010','1018','1020','1006','2007','2008','2009','10503')

Group by doc_id;

Thanks,

AS

[maleksafa]

load empid, max(sqno)

Resident table1

where empid = 12

group by empid

you should know that group by only works on Resident table and it does not work when reading directly from QVD.

[Anonymous]

Hi Guys please find the  attachments....

In the data consists like

EMp Id,

Serial No,

Level Code

From this

If I select a single id I get multiple Serial no's but I want the max serial no only

[Anonymous]

i'm used the code like

Load

EmpID,
[Serial No],
     [Level Code]
FROM

(ooxml, embedded labels, table is Sheet1) Where [Serial No] = max([Serial No]) group by EmpID;

but i'm getting error..........

Please help guys.

[Clever_Anjos]

Please try this

tmp:

Load

EmpID,
[Serial No],
     [Level Code]
FROM

(ooxml, embedded labels, table is Sheet1) ;

inner keep LOAD

EmpID,

Max([Serial No]) as [Serial No]

resident tmp

group by EmpID

[Anonymous]

Hi Anjos,

Its worked for me ...

Is there any way to do it .............

[senpradip007]

PFA. Hope it will meet your purpose.