how would you join these Tables

[#1]

i have three tables  i don't want to use any  joins here

Table1:

UserID

DealarCode

Date

Ammount

UserID_1

DealarCode 1

01-01-14

2214

Table2:

UserID

DealarCode

Date

Quantity

UserID_1

DealarCode 1

01-01-14

36

Table3:

UserID

DealarCode

Date

Tax

UserID_1

DealarCode 1

01-01-14

115

am using  Concatenate

and getting this result

UserID	DealarCode	Date	Ammount	Quantity	Tax
UserID_1	DealarCode 1	01-01-14	2214
UserID_1	DealarCode 1	01-01-14		36
UserID_1	DealarCode 1	01-01-14			115

i want result like this

UserID	DealarCode	Date	Ammount	Quantity	Tax
UserID_1	DealarCode 1	01-01-14	2214	36	115

[alexandros17]

MyTable:

load * resident Table1;

join

load * resident Table2;

join

load * resident Table3;

But you'll obtain the same result leaving the 3 tables as they are, let them associate (with a syntetic key)

[AbhijitBansode]

Use below script:

Load
      UserID,
      DealerCode,
      Date,
      Amount               AS Measure
      'Amount'             AS Type
 From Table1;
 Concatenate
 Load
      UserID,
      DealerCode,
      Date,
      Quantity             AS Measure
      'Quantity'           AS Type
 From Table2;
 Concatenate
 Load
      UserId,
      DealerCode,
      Date,
      Tax                   AS Measure
      'Tax'                 AS Type
 From Table3;
 
 
 
 Use Set analysis for expressions like:
 
 Amount: sum ({<Type={'Amount'}>}Measure) 

[hic]

You could do it in two steps, first concatenate, then aggregate:

TempTable:

Load * From Table1;

Concatenate Load * From Table2;

Concatenate Load * From Table3;

Final:

Noconcatenate

Load UserID, DealarCode, Date,

     Sum(Ammount) as Amount,

     Sum(Quantity) as Quantity,

     Sum(Tax) as Tax

     Resident TempTable

     Group By UserID, DealarCode, Date;

Drop Table TempTable;

HIC

[er_mohit]

Try this code

Table1:

LOAD UserID & '|'&DealarCode&'|'&Date as Key2, * Inline [

UserID, DealarCode, Date, Ammount,

UserID_1, DealarCode 1, 01-01-14, 2214

];

Table2:

LOAD

UserID & '|'&DealarCode&'|'&Date as Key2,

* Inline [

UserID, DealarCode, Date, Quantity

UserID_1, DealarCode 1, 01-01-14, 36

];

Table3:

LOAD UserID & '|'&DealarCode&'|'&Date as Key2,* Inline [

UserID, DealarCode, Date, Tax

UserID_1, DealarCode 1, 01-01-14, 115

];

DROP Fields UserID,DealarCode,Date From Table1;

DROP Fields UserID,DealarCode,Date From Table2;

[#6]

hi all  i have done like this

Quantity:
Mapping LOAD 
     UserID & DealarCode & Date as  Key,
     Quantity
FROM
Quantity

Tax:
Mapping LOAD 
     UserID & DealarCode & Date as  Key,
     Tax
FROM
Tax

Ammount:
LOAD 
     UserID, 
     DealarCode, 
     UserID & DealarCode as  MKey,
     UserID & DealarCode & Date as  Key,
     Date, 
     Ammount
FROM
Ammount

All:
LOAD * ,
ApplyMap('Quantity',Key,'0') as 'Quantity',
ApplyMap('Tax',Key,'0') as 'Tax'
Resident Ammount;
DROP Table Ammount;