Issue with Resident load and Concat()

[Anonymous]

Hi I want to concatenate multiple rows to give me a single row for a problem number.

Since there are multiple Incident Reference values for a single Problem Number. Besides concatenating the rows to give a single row of Problem number and the respective incident counts I also need to display other corresponding fields.

When i use resident and concat() function i am unable to load the remaining fields, besides Problem Number, Incident Count,Incident Reference  in the table where i am concatenating the rows. It throws an error saying invalid load.

Please can somebody help

Original:

LOAD Problem_Number,
Assignment_Group,
Problem_Summary,
Problem_Description,
Problem_Open_Date,
Problem_Status,
Resolution_Details,
Problem_Resolve_Date,
ADD_Fix_Status,
Comments_from_ADD_Meeting,
RTC_Number,
RTC_Status,
RTC_Summary,
Release_Details,
Incident_Reference


FROM
Sourcefile;

load


distinct(Problem_Number) as Problem_Number,
concat(distinct(Incident_Reference),',') as Incident_Reference,
count(DISTINCT(Incident_Reference)) as Incident_Count

Resident Original
Group by Problem_Number;

drop Table Original;

[cesaraccardi · Accepted solution]

Hi,

Try removing the distinct() function from your loading this is not required when the field Problem_Number is being used in the 'Group by' clause.

[hic]

There is no distinct function. "distinct" is a clause that can be used inside a Load or an aggregation function, e.g.

     Load distinct ... from

     Count(distinct Incident_Reference)

So, just as Cesar suggests, you should remove the "distinct" from the second Load. Further, you should not drop the first table. Keep both tables. Finally, you should rename the field that the Concat() aggregation creates. Hence:

load
     Problem_Number,
     concat( distinct Incident_Reference ,',') as Incident_References,
     count( distinct Incident_Reference) as Incident_Count
     Resident Original
     Group by Problem_Number;

HIC

[Anonymous]

It worked and Thanks

[Anonymous]

It worked.. Thanks a lot