Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

jonah-vq
New Contributor III

Create new field in loadscript based on values other field

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

PersonAdres_TypeAdres
1EML1@gmail.com
1GSM123456
1TEL456789
2EML2@gmail.com
2GSM987654
2TEL654321

Table_Visual

PersonAdres_EMLAdres_GSMAdres_TEL
11@gmail.com123456456789
22@gmail.com987654654321

Table_WrongVisual   

PersonAdres_EMLAdres_GSMAdres_TEL
11@gmail.com--
1-123456-
1--456789
22@gmail.com--
2-987654-
2--

654321

Thank you!

1 Solution

Accepted Solutions
daveamz01
Contributor III

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

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

5 Replies
daveamz01
Contributor III

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

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

vinieme12
Esteemed Contributor II

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

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

];

vinieme12
Esteemed Contributor II

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

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;

jonah-vq
New Contributor III

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

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

PersonAdres_TypeAdres
1EML1@gmail.com
1EML1@hotmail.com
1EML1@yahoo.com
1GSM

123456

1TEL456789
2EML2@gmail.com
2GSM987654
2TEL654321

Table_Visual

PersonAdres_EMLAdres_EML1Adres_EML2Adres_GSMAdres_TEL
11@gmail.com1@hotmail.com1@yahoo.com123456456789
22@gmail.com987654654321
vinieme12
Esteemed Contributor II

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

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;

Community Browser