topic Re: Complex concatinate in QlikView

Complex concatinate

sculptorlv — Wed, 25 Nov 2020 16:16:04 GMT

Hello!

I have the following data:

Table:1

Agent_Name Group_Code

Alex Delta

Anna Delta

John Alfa

Simon Alfa

How can I use QLIK LOAD in order to gen an additional column Group_Name:

Table_to_be_created:

Group_Code Group_Name

Delta Alex and Anna

Alfa John and Simon

Thank you in advance!

Re: Complex concatinate

avkeep01 — Wed, 24 Oct 2018 13:50:01 GMT

In this case you'll need a join instead of a concatenate.

Table1:

Agent_Name,

Group_Code

FROM Table1;

LEFT JOIN (Table1) LOAD

Group_Code ,

Group_Name

FROM Table2

Re: Complex concatinate

sculptorlv — Wed, 24 Oct 2018 13:53:29 GMT

Sorry, I wasn't very precise,

I don't have a Group_Name column and I need to create it.

Re: Complex concatinate

avkeep01 — Wed, 24 Oct 2018 14:02:17 GMT

Table1:

Agent_Name,

Group_Code

FROM Table1;

LEFT JOIN (Table1) LOAD

Group_Code,

CONCAT(Agent_Name,' and ') AS Group_Name

RESIDENT Table1

GROUP BY Group_Code;

Re: Complex concatinate

sculptorlv — Wed, 24 Oct 2018 14:09:22 GMT

Thank you for help! It works with slight modification!

My exact code is:

SQL_AGENT_TEAMS:

LOAD

Team_Code,

CONCAT(Employee_Code, ' and ') AS Team_Name

RESIDENT SQL_AGENT_TEAMS

GROUP BY

Team_Code;

Re: Complex concatinate

vishsaggi — Wed, 24 Oct 2018 14:34:20 GMT

may be try this?

Grouping:

LOAD * INLINE [

Agent_Name, Group_Code

Alex, Delta

Anna, Delta

John, Alfa

Simon,Alfa

];

NoConcatenate

Final:

LOAD Group_Code,

Concat(Agent_Name, ' and ') AS Group_Name

Resident Grouping

Group By Group_Code;

Drop table Grouping;