topic Re: Merge column in QlikView

Merge column

Wed, 23 Nov 2016 05:26:15 GMT

Hello All,

I have a table as below in excel:

Name	Tel1	Tel2	Tel3
A	123456789
B	123456789	456789321
C	123456789	456789323
D	123456789	456789324	54784562
E	123456789		54784568
F	123456789		54784569
G	123456789
H	123456789

And I need to add another column like to merge the tables as shown below:

Name	TEL
A	123456789
B	123456789
B	456789321
C	123456789
C	456789323
D	123456789
D	456789324
D	54784562
E	123456789
E	54784568
F	123456789
F	54784569
G	123456789
H	123456789

Can you please advise me on the best method to do this?

Many Thanks,

Hasvine

Re: Merge column

tresB — Wed, 23 Nov 2016 05:38:58 GMT

Try concatenating them loading separately like:

Load

Name,

Tel1 as TEL

From <>;

Load

Name,

Tel2 as TEL

From <>;

Load

Name,

Tel3 as TEL

From <>;

Re: Merge column

tamilarasu — Wed, 23 Nov 2016 05:47:31 GMT

Hi Hasvine,

You can also use cross table. Kindly check the attached file.

Data:
CrossTable(Tel, Data)
LOAD Name,
Tel1,
Tel2,
Tel3
FROM

Excel.xlsx
(ooxml, embedded labels, table is Sheet1);

DROP Field Tel;

RENAME Field Data To Tel;

Re: Merge column

Wed, 23 Nov 2016 06:18:32 GMT

Hello Tamil,

I am trying to load it as below but I dont seem to get it right:

Can you help me with this?

Note that my [Téléphone bureau] = Tel1

[Téléphone domicile] = Tel2

[Tél mobile] = Tel3

Data:

CrossTable(Tel, Data)

LOAD Titre,

Nom,

Prénom,

Service,

Titre1,

[Téléphone bureau],

[Téléphone domicile],

[Tél mobile],

[Adresse de messagerie],

CONCESSION

FROM

[..\Annuaire\SHLMR_ANNUAIRE.xls]

(biff, embedded labels, table is [shlmr plateau caillou$]);

DROP Field Tel;

RENAME Field Data To Tel;

Re: Merge column

tamilarasu — Wed, 23 Nov 2016 06:46:13 GMT

Hasvine,

Something like below.

Data:

CrossTable(Téléphone, Data,7)

LOAD Titre,

Nom,

Prénom,

Service,

Titre1,

[Adresse de messagerie],

CONCESSION,

[Téléphone bureau],

[Téléphone domicile],

[Tél mobile],

FROM

[..\Annuaire\SHLMR_ANNUAIRE.xls]

(biff, embedded labels, table is [shlmr plateau caillou$]);

DROP Field Téléphone;

RENAME Field Data To Téléphone;

Re: Merge column

ganeshsvm — Wed, 23 Nov 2016 06:52:48 GMT

Hello Hasvine,

Tamil Nagaraj did give you a proper solution, according to your data, you can use the following script:

First, Identify the primary field in your table, suppose if, "Titre" is the PK, then,

Table1:

LOAD Titre,

Nom,

Prénom,

Service,

[Adresse de messagerie],

CONCESSION

FROM

[..\Annuaire\SHLMR_ANNUAIRE.xls]

(biff, embedded labels, table is [shlmr plateau caillou$]);

Table2:

CrossTable(Tel, Data)

LOAD Titre,

[Téléphone bureau],

[Téléphone domicile],

[Tél mobile]

FROM

[..\Annuaire\SHLMR_ANNUAIRE.xls]

(biff, embedded labels, table is [shlmr plateau caillou$]);

DROP Field Tel;

RENAME Field Data To Tel;

Then, you can Join or concatenate both table or just leave it which will associate itself with key field.

Regards,

Ganesh

Re: Merge column

Wed, 23 Nov 2016 07:50:00 GMT

Hello everyone,

Thank you all for your help. 🙂

The script of Ganesh working great.

Thanks,

Hasvine