Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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.
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.
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
It worked and Thanks
It worked.. Thanks a lot