Concatenation Problem

[#1]

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

[SunilChauhan]

so is not helping.

can you explain a bit more

thanks

[#3]

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

[SunilChauhan]

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

[#5]

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$);

[SunilChauhan]

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

[jagan]

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.

[#8]

Hi Sunil ,

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

I find that  it weird

Thank U

[SunilChauhan]

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

thanks

[#10]

I solve the problem , i add , GROUPE BY Pivot_Nom

But i dont have a units

Please See att

Thank u man