topic Re: Concatenation Problem in QlikView

Concatenation Problem

Mon, 24 Feb 2014 14:57:55 GMT

Hello , I have 2 table :

HSBC table ,

Name	Hsbc Identifier	Units
ANJUERRE SOPHIE	11100_FRA0000990158	1222
ANJUERRE SOPHIE	11100_FRA0000980114	280

Pivot Table

NAME	Hsbc Identifier 1	Hsbc Identifier 2
ANJUERRE SOPHIE	11100_FRA0000990158	11100_FRA0000980114

I want to visualise This

NAME	Hsbc Identifier 1	Hsbc Identifier 2	Sum(Units)
ANJUERRE SOPHIE	11100_FRA0000990158	11100_FRA0000980114	1502

Some One can help me please , i debute in QV

please See attached

Re: Concatenation Problem

christophebrault — Mon, 24 Feb 2014 15:04:22 GMT

Hi,

// You can do something like this :

Load your first table :

TABLE:

LOAD Name,

Hsbc_identifier,

...,

Units

FROM ... ;

If you Want to sum the units and have only one line per Name value, you'll have to "group by" :

NoConcatenate

RESULT:

LOAD Name,

Sum(Units) as Units

Resident TABLE Group By Name;

drop table TABLE;

// Left join additionnals infos from another file :

Left Join

LOAD Name, // Will left join on this field

Hsbc_Identifier_1,

Hsbc_Identifier_2

FROM ... ,

Re: Concatenation Problem

Mon, 24 Feb 2014 15:08:23 GMT

Hey Christophe ,

I dont intersteand very well yourt solution

Re: Concatenation Problem

Mon, 24 Feb 2014 15:22:51 GMT

Can U give me a example please

I attached , a Qv and xls file

Re: Concatenation Problem

Mon, 24 Feb 2014 15:23:18 GMT

Please check the attachment is it serves your purpose.

Re: Concatenation Problem

Mon, 24 Feb 2014 15:59:15 GMT

Hey Sudoh

Your script doesnt work when i add a rows to my pivot table

Re: Concatenation Problem

Mon, 24 Feb 2014 16:19:55 GMT

Does it give any error?

Can you check the path of your files? - I worked out the script based on my local path - however, I changed the path to yours while reposting.

Alternatively, can you share a slightly bigger data - maybe 4 or 5 rows?

Re: Re: Concatenation Problem

Tue, 25 Feb 2014 15:14:30 GMT

Hi Subodh ,

I employeed your solution with a other project , but its doesnt work , do you have a subjection ? Please see the attachement thank u a lot

Re: Re: Re: Concatenation Problem

SunilChauhan — Tue, 25 Feb 2014 15:27:52 GMT

see for ur sample data u can implement below

tab1:

Load * inline [

Name, HsbcIdentifier, Units

ANJUERRE SOPHIE, 11100_FRA0000990158 ,1222

ANJUERRE SOPHIE, 11100_FRA0000980114, 280

];

[Pivot Table]:

Load * inline [

NAME ,Hsbc Identifier 1 ,Hsbc Identifier 2

ANJUERRE SOPHIE, 11100_FRA0000990158, 11100_FRA0000980114

];

left Join

Load Name,

Sum(Units) as Units

resident tab1 group by Name;

drop table tab1;

u can apply same logic on xls data.

see the attached file

hope this helps

Re: Re: Re: Concatenation Problem

Tue, 25 Feb 2014 15:49:55 GMT

Hi Sunil ,

i want something like this , For each CCN , show the quantite of parts

Re: Concatenation Problem

SunilChauhan — Tue, 25 Feb 2014 15:52:11 GMT

so is not helping.

can you explain a bit more

thanks

Re: Re: Concatenation Problem

Tue, 25 Feb 2014 16:02:35 GMT

I Laod Two tables

My first table

Nominatif :

Name	CCN	Units
Jonathan COHEN	9624	2000
Jonathan COHEN	24092	2001
Xavier Fontanet	4004	12
Xavier Fontanet	4004	456
Xavier Fontanet	6423	299
Xavier Fontanet	2122	5000

Pivot

Name	CCN1	CCN2	CCN3
Jonathan COHEN	9624	24092
Xavier Fontanet	4004	6423	2122

For each CCN number I want to display the number of units all in keeping my modélisation Star

Please See attached ,

I hope that you understood my problem , do not hesitate if you have a other question

Re: Concatenation Problem

SunilChauhan — Tue, 25 Feb 2014 16:16:46 GMT

Nominatif :

Load Name,CCN,Units

from Tablename;

Pivot:

Load Name,

CCN1 as CCN

from Tablename

Concatenate

Load Name,

CCN2 as CCN

from Tablename

Concatenate

Load Name,

CCN3 as CCN

from Tablename

left join

Load name

sum(Units) as Units

resident Nominatif ;

drop table Nominatif ;

hope this helps

Re: Re: Concatenation Problem

Tue, 25 Feb 2014 16:55:00 GMT

Sunil ,

Thank u , but i have a little problem ,

SET ThousandSep=' ';
SET DecimalSep=',';
SET MoneyThousandSep=' ';
SET MoneyDecimalSep=',';
SET MoneyFormat='# ##0,00 €;-# ##0,00 €';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET MonthNames='janv.;févr.;mars;avr.;mai;juin;juil.;août;sept.;oct.;nov.;déc.';
SET DayNames='lun.;mar.;mer.;jeu.;ven.;sam.;dim.';




Table1:


LOAD [CODE EMETTEUR], 
     CCN ,
     [CODE VALEUR], 
     EUROCLEAR, 
     [NOMBRE DE TITRES EN VOTE SIMPLE] 
FROM

(biff, embedded labels, table is Feuil1$);


temp:


LOAD
 @3 as Pivot_Nom, 
 @4 as Pivot_Prenom, 
 @11 as CCN

 FROM

(biff, no labels, table is Feuil1$);


Concatenate
LOAD
@3 as Pivot_Nom, 
@4 as Pivot_Prenom, 
@12 as CCN


FROM

(biff, no labels, table is Feuil1$);


Concatenate


LOAD
@3 as Pivot_Nom, 
@4 as Pivot_Prenom,
@13 as CCN


FROM

(biff, no labels, table is Feuil1$);


Concatenate


LOAD
@3 as Pivot_Nom, 
@4 as Pivot_Prenom,
@14 as CCN


FROM

(biff, no labels, table is Feuil1$);




left join


Load


@3 as Pivot_Nom, 
@4 as Pivot_Prenom,


sum([NOMBRE DE TITRES EN VOTE SIMPLE]) as Units


resident Table1 ;
drop table Table1 ;











// Pivot:


//LOAD 
//
// @3 as Pivot_Nom, 
//   @4 as Pivot_Prenom, 
//     @5 as Pivot_Datenaissance, 
//     @6 as Pivot_Matricule, 
//     @7 as Pivot_NouvelleMatriculation, 
//     @8 as Pivot_CodeValoptec, 
//     @9 as Pivot_NumeroSecuriteSocial, 
//     @10 as Pivot_MatriculePee, 
//     @11 as Pivot_CCN1, 
//     @12 as Pivot_CCN2, 
//     @13 as Pivot_CCN3, 
//     @14 as Pivot_CCN4,  
//     //LOOKUP('Nb total de parts', 'CCN', [@11], 'TABLE1') + LOOKUP('Nb total de parts', 'CCN', [@12], 'TABLE1') as No_Of_Units,
//     @15 as Pivot_HolderIdentifier, 
//     @16 as Pivot_IdTitulaireSG, 
//     @17 as Pivot_NumSSInt1, 
//     @18 as Pivot_NumSsInt2, 
//     @19 as Pivot_MatriculeEntreprise, 
//     @20 as Pivot_HSBCNumIdentifiant2, 
//     @21 as Pivot_EsapEmployee, 
//     @22 as Pivot_EsppID, 
//     @23 as Pivot_IrishEmplyRef, 
//     @24 as Pivot_KoreeWorkerID, 
//     @25 as Pivot_ChemiglassId,
//     @32 as Pivot_ref2
//FROM
//
//(biff, no labels, table is Feuil1$);

Re: Concatenation Problem

SunilChauhan — Wed, 26 Feb 2014 02:57:06 GMT

hi,

@3 and @4 are not available in Table. thats why this error.Now you see which column are equivalent to @3 and @4 in Table one and use accrodingly.

hope this helps

Re: Concatenation Problem

jagan — Wed, 26 Feb 2014 04:09:45 GMT

Hi,

Try this simple script

Temp:

Load * inline [

Name, HsbcIdentifier, Units

ANJUERRE SOPHIE, 11100_FRA0000990158 ,1222

ANJUERRE SOPHIE, 11100_FRA0000980114, 280

];

DAta:

LOAD

Name,

Units,

HsbcIdentifier,

SubField(HsbcIdentifier, ',', 1) AS HsbcIdentifier1,

SubField(HsbcIdentifier, ',', 2) AS HsbcIdentifier2;

//Mid(HsbcIdentifier, 0, Index(HsbcIdentifier, ',')) AS HsbcIdentifier1,

//Mid(HsbcIdentifier, Index(HsbcIdentifier, ',')-1) AS HsbcIdentifier2;

LOAD

Name,

Sum(Units) AS Units,

Concat(HsbcIdentifier, ',') AS HsbcIdentifier

RESIDENT Temp

Group By Name;

Drop table Temp;

Regards,

Jagan.

Re: Concatenation Problem

Wed, 26 Feb 2014 08:58:55 GMT

Hi Sunil ,

@3 is correspending to my field pivot_name in my table temp??

I find that it weird

Thank U

Re: Concatenation Problem

SunilChauhan — Wed, 26 Feb 2014 09:37:29 GMT

No i mean to say you can chech field in Table 1 which has same data as @3 and @4 or attached sample .

thanks

Re: Re: Concatenation Problem

Wed, 26 Feb 2014 09:41:37 GMT

I solve the problem , i add , GROUPE BY Pivot_Nom

But i dont have a units

Please See att

Thank u man