Error: Group By in Load Statement

[#1]

Hi All,

I am trying to run the following load statement with group by. Can anyone please tell me the right way to run this?

LOAD ID,

          'ABC' AS RecordType,

            DATE1,

                DATE2,

                     DATE3,

RESIDENT InventionChangeDates

where ([Date1  <>NULL() and Date2 <>null() and Date3<> NULL())

group by ID;

So basically I am trying to load only those ID's where all the 3 dates(Date1, Date2, DAte3) are not null. So if any one or 2 is null, that's fine but if all the dates corresponding to that ID's are null , then it shoul not be reloaded.

Thanks!! Any help would be appreciated.

[swuehl]

A group by load will require that you use aggregation functions on all input table fields that are not listed in the group by clause (i.e. DATE1, DATE2, DATE3).

But it seems to me that you don't want or need to group your records by ID, so why not just remove the 'group by ID'?

[MayilVahanan]

Hi,

     For group , the field(s) should be include in the table. For your requirement, i think no need for grouping because you not use aggregation function.Without grouping itself , it will work fine.

     For example,

     Load Week, ArtNo, round(Avg(TransAmount),0.05) as WeekArtNoAverages from table.csv group by Week, ArtNo;

Hope it helps

[#4]

Thanks guys. I did try that statement without the Group By clause but it didn't work. So I added the group by clause.

Now when I made this change in the where clause and it worked. Can you guys please tell me the logic behind this?

LOAD ID,

          'ABC' AS RecordType,

            DATE1,

                DATE2,

                     DATE3,

RESIDENT InventionChangeDates

where NOT ([Date1  = NULL() and Date2 = null() and Date3= NULL());

Thanks a lot.

[swuehl]

I admit I haven't paid much attention to the where clause (and I think there is also a comma too much after the last loaded field).

These two where clauses have two different logics, the last one returns all records where not all dates are null, that's what you want. The first one returns all records where all dates are not null, thus removing records with only one or two null dates.