topic Re: Issue with Resident load and Concat() in QlikView

Issue with Resident load and Concat()

Anonymous — Fri, 23 Jan 2015 10:08:28 GMT

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;

Re: Issue with Resident load and Concat()

cesaraccardi — Fri, 23 Jan 2015 10:43:22 GMT

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.

Re: Issue with Resident load and Concat()

hic — Fri, 23 Jan 2015 13:55:10 GMT

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

Re: Issue with Resident load and Concat()

Anonymous — Thu, 11 Jun 2015 10:33:40 GMT

It worked and Thanks

Re: Issue with Resident load and Concat()

Anonymous — Thu, 11 Jun 2015 10:33:53 GMT

It worked.. Thanks a lot