Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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)
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)
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
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;
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;