2 sql tables joined and hierarchized

[#1]

Dear All,

there are 2 sql tables: Table_1 includes transactions, Table_2 includes accounts hierarchy like below.

Table_1

T1_AccountId, T1_Amount

2, 51

3, 61

Table_2

T2_AccountId, T2_AccountParentId, T2_AccountName

1, , Sales

2, 1, Apples

3, 1, Pears

I need to join and hierarchize them like below

Table_Joined

AccountId, AccountName, AccountName1, Amount

2, Sales, Apples, 51

3, Sales, Pears, 61

The problem is that when the code is defined as

HIERARCHY (T2_AccountId, T2_AccountParentId, T2_AccountName)

SELECT T2_AccountId, T2_AccountParentId, T2_AccountName, T1_Amount

FROM Table_1 LEFT JOIN Table_2 ON Table_1.AccountId = Table_2.AccountId

the majority of data gets lost and when RIGHT JOIN is used HIERARCHY gets lost. Due to the other parts of the code the two tables must be joined into one. Do you have an idea how to make it right?

BR,

Przemek

[mantaq10]

Hi Przemek,

One way to do this would be join the tables first and then create the hierarchy load from the resident table.

like this:

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Table1:
LOAD * INLINE [

AccountID, Amount
2,51
3,61
];

inner join
Table2:
LOAD * INLINE [

AccountID, AccountParentID, AccountName
1,,Sales
2,1,Apples
3,1,Pears
];


hierarchy(AccountID, AccountParentID, AccountName) load *
resident Table1;

drop table Table1;

[mantaq10]

And here is the attached qvw...