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

[#1]

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 NUMBER	OPTFIELD	VALUES
IN00001	OEINV	Education
IN00001	OEPROJ	Vic Primary School
IN00002	OEINV	Hospital
IN00002	OEPROJ	Vic Hospital

I need it to look like this:

INV NUMBER	OEINV (SECTOR)	OEPROJ (PROJECT)
IN00001	Education	Vic Primary School
IN00002	Hospital	Vic Hospital

How would I do this in the Qlik Sense script?

Thanks

Carmen

[antoniotiman]

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;

[#3]

Hi Antonio

Thank you.

I used the Generic prefix.

[Anonymous]

Yes Geneic load will work above .