Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

How to split one field into two fields based on output of another field

Hi

I have 1 field called OPTFIELD that contains two values/options,

  1. OEINV which represents Sectors ie. Education
  2. OEPROJ which represents Projects ie. Victoria Primary School

Then I have a field called VALUES that contains the descriptions for the Sectors and Projects.

To visualise this in a table, it currently looks like this:

   

INV NUMBEROPTFIELDVALUES
IN00001OEINVEducation
IN00001OEPROJVic Primary School
IN00002OEINVHospital
IN00002OEPROJVic Hospital

I need it to look like this:

INV NUMBEROEINV (SECTOR)OEPROJ (PROJECT)
IN00001EducationVic Primary School
IN00002HospitalVic Hospital

How would I do this in the Qlik Sense script?

Thanks

Carmen

Tags (1)
3 Replies
antoniotiman
Not applicable

Re: How to split one field into two fields based on output of another field

Hi Carmen,

Temp:
Load * Inline [
INV NUMBER, OPTFIELD, VALUES
IN00001, OEINV, Education
IN00001, OEPROJ, Vic Primary School
IN00002, OEINV, Hospital
IN00002, OEPROJ, Vic Hospital
]
;

// First solution
Generic LOAD * Resident Temp;
Drop Table Temp;
--------------------------------------------------------------------------------------------------------------------------------------------
// Second solution
Left Join
LOAD [INV NUMBER],VALUES as OEINV
Resident Temp
Where OPTFIELD = 'OEINV';
Left Join
LOAD [INV NUMBER],VALUES as OEPROJ
Resident Temp
Where OPTFIELD = 'OEPROJ';

Table:
LOAD DISTINCT [INV NUMBER],OEINV,OEPROJ
Resident Temp;
Drop Table
Temp;

Not applicable

Re: How to split one field into two fields based on output of another field

Hi Antonio

Thank you.

I used the Generic prefix.

commonqlik
Not applicable

Re: How to split one field into two fields based on output of another field

Yes Geneic load will work above .