Table Transformation issue

[mjayachandran]

Hi All,

Need your help. I have the Table as shown below:

Input
Company	DisplayName	Status	AppAccess
ABC	Name1	Active	App1 | App2 | App3
BCD	Name2	Active	App2 | App5
CDE	Name3	InActive	App10 | App4
DEF	Name4	Active	App8 | App 7
EFG	Name5	Active	App2 | App19

I'm trying to get the table below:

Output
Company	DisplayName	Status	AppAccess
ABC	Name1	Active	App1
ABC	Name1	Active	App2
ABC	Name1	Active	App3
BCD	Name2	Active	App2
BCD	Name2	Active	App5
CDE	Name3	InActive	App10
CDE	Name3	InActive	App4
DEF	Name4	Active	App8
DEF	Name4	Active	App 7
EFG	Name5	Active	App2
EFG	Name5	Active	App19

Thanks in Advance.

Mahesh Jayachandran

[ashfaq_haseeb · Accepted solution]

Hi

use Subfield() function

Load *,SubField(AppAccess,'|') as AppAccess_New;

load * inline

[

Company,DisplayName,Status,AppAccess

ABC,Name1,Active,"App1|App2|App3"

BCD,Name2,Active,"App2|App5"

CDE,Name3,InActive,"App10|App4"

DEF,Name4,Active,"App8|App7"

EFG,Name5,Active,"App2|App19"

];

Have a look at attached application

Regards

ASHFAQ

[MK_QSL]

Input:

Load 

  Company,

  DisplayName,

  Status,

  Trim(Subfield(AppAccess,'|')) as AppAccess

Inline

[

  Company,DisplayName,Status, AppAccess

  ABC, Name1, Active, App1 | App2 | App3

  BCD, Name2, Active, App2 | App5

  CDE, Name3, InActive, App10 | App4

  DEF, Name4, Active, App8 | App 7

  EFG, Name5, Active, App2 | App19

];

[mdmukramali]

Dear mahesh,

kindly you try below script

Company:

LOAD * Inline

[

Company,DisplayName,    Status,    AppAccess

ABC,    Name1,    Active,    App1 | App2 | App3

BCD,    Name2    ,Active,    App2 | App5

CDE,    Name3,    InActive,    App10 | App4

DEF    ,Name4    ,Active,    App8 | App7

EFG    ,Name5,    Active,    App2 | App19

];

NoConcatenate

LOAD Company,DisplayName,Status,

subfield(AppAccess, '|') AS AppAccess

Resident  Company;

DROP Table Company;

[mjayachandran]

Thank you all. Its works.