topic Re: Create new field in loadscript based on values other field in App Development

Create new field in loadscript based on values other field

Anonymous — Wed, 26 Apr 2017 10:41:57 GMT

Hello,

I have a Table_Source with information per row that I want to show per type in Qlik (see Table_Visual😞

What I did now was in the loadscipt to create a separate table per information type with the where clause (so one table for EML, one for GSM and one for TEL). Then in QLik I could create a table with the newly created fields.

If I created the fields in the same Table_Source, the the result was not what I wanted Table_WrongVisual.

Is there any other way that I can do this in Qlik itself without modifiying the script (DataPivot?) ? Is there a more efficient way in the load script than creating a table field?

Table_Source

Person	Adres_Type	Adres
1	EML	1@gmail.com
1	GSM	123456
1	TEL	456789
2	EML	2@gmail.com
2	GSM	987654
2	TEL	654321

Table_Visual

Person	Adres_EML	Adres_GSM	Adres_TEL
1	1@gmail.com	123456	456789
2	2@gmail.com	987654	654321

Table_WrongVisual

Person	Adres_EML	Adres_GSM	Adres_TEL
1	1@gmail.com	-	-
1	-	123456	-
1	-	-	456789
2	2@gmail.com	-	-
2	-	987654	-
2	-	-	654321

Thank you!

Re: Create new field in loadscript based on values other field

daveamz — Wed, 26 Apr 2017 10:48:14 GMT

Hi Jonah,

Try something like

Table:

LOAD Person,

Adres_Type,

Adres

FROM...

WHERE Adres_Type = 'EML'

LEFT JOIN (Table)

LOAD Person,

Adres AS Adres_GSM

FROM ...

WHERE Adres_Type = 'GSM'

LEFT JOIN (Table)

LOAD Person,

Adres AS Adres_TEL

FROM ...

WHERE Adres_Type = 'TEL'

Regards,

David

Re: Create new field in loadscript based on values other field

vinieme12 — Wed, 26 Apr 2017 11:30:05 GMT

Another way

TEMP:

LOAD Person,

MaxString(Adres_EML) as Adres_EML,

MaxString(Adres_GSM) as Adres_GSM,

MaxString(Adres_TEL) as Adres_TEL

Group by Person;

LOAD

Person,

if(Adres_Type='EML',Adres) as Adres_EML,

if(Adres_Type='GSM',Adres) as Adres_GSM,

if(Adres_Type='TEL',Adres) as Adres_TEL

;

LOAD * INLINE [

Person,Adres_Type,Adres

1,EML,1@gmail.com

1,GSM,123456

1,TEL,456789

2,EML,2@gmail.com

2,GSM,987654

2,TEL,654321

];

Re: Create new field in loadscript based on values other field

vinieme12 — Wed, 26 Apr 2017 11:36:58 GMT

Here is a Robust version for Joining the tables

Temp:

LOAD * INLINE [

Person,Adres_Type,Adres

1,EML,1@gmail.com

1,GSM,123456

1,TEL,456789

2,EML,2@gmail.com

2,GSM,987654

2,TEL,654321

];

FACT:

LOAD DISTINCT Person

RESIDENT Temp;

For i = 1 to 3

vFilter= Pick($(i),'EML','GSM','TEL');

JOIN(FACT)

LOAD Person,

Adres as '$(vFilter)'

RESIDENT Temp

Where Adres_Type = '$(vFilter)';

Next i;

Drop table Temp;

Re: Create new field in loadscript based on values other field

Anonymous — Wed, 26 Apr 2017 14:53:19 GMT

I tried this too and it worked great! Thanks alot (as a Qlik newcommer I still have alot to learn )

How could i create a second/third/... EML field if my source data is the following :

Table_Source

Person	Adres_Type	Adres
1	EML	1@gmail.com
1	EML	1@hotmail.com
1	EML	1@yahoo.com
1	GSM	123456
1	TEL	456789
2	EML	2@gmail.com
2	GSM	987654
2	TEL	654321

Table_Visual

Person	Adres_EML	Adres_EML1	Adres_EML2	Adres_GSM	Adres_TEL
1	1@gmail.com	1@hotmail.com	1@yahoo.com	123456	456789
2	2@gmail.com			987654	654321

Re: Create new field in loadscript based on values other field

vinieme12 — Wed, 26 Apr 2017 19:28:31 GMT

Sample

Temp:

LOAD * INLINE [

Person,Adres_Type,Adres

1,EML,1@gmail.com

1,EML,1_2@gmail.com

1,EML,1_3@gmail.com

1,GSM,123456

1,GSM,1_123456

1,TEL,456789

2,EML,2@gmail.com

2,EML,2_1@gmail.com

2,GSM,987654

2,TEL,654321

];

FACT:

LOAD DISTINCT Person

RESIDENT Temp;

For i = 1 to 3

vFilter= Pick($(i),'EML','GSM','TEL');

JOIN(FACT)

LOAD Person

,Subfield($(vFilter),'; ',1) as '$(vFilter)_1'

,Subfield($(vFilter),'; ',2) as '$(vFilter)_2'

,Subfield($(vFilter),'; ',3) as '$(vFilter)_3'

;

LOAD Person,

concat(DisTINCT Adres,'; ') as '$(vFilter)'

RESIDENT Temp

Where Adres_Type = '$(vFilter)'

Group by Person;

Next i;

Drop table Temp;