topic Re: Inner Join (tricky one) in QlikView

Inner Join (tricky one)

Sun, 18 Mar 2012 14:04:00 GMT

Hi All,

I need to inner join two tables: DBS and ACC_TYPE where ACC_TYPE gets data from databases that are listed in DBS. Please take a look at the below code and suggest.

Thank you,

Przemek

DBS:
SELECT DB_Name FROM Configuration;
ACC_TYPE:
LET LiczbaBaz = NoOfRows('DBS');
FOR i=0 TO $(LiczbaBaz)-1
LET DB_Name = Peek('DB_Name', $(i), 'DBS');
SELECT
'$(DB_Name)' AS DB_Name, 
CASE WHEN Dim_1 = 1 THEN 'ER' WHEN Dim_1 = 2 THEN 'KP' WHEN Dim_1 = 3 THEN 'KH' END AS AccTypeName
FROM CDN_$(DB_Name).CDN.Company WHERE Dim_1 = 3;
NEXT

Re: Inner Join (tricky one)

jonathandienst — Sun, 18 Mar 2012 16:27:16 GMT

Przemek

This should work:

DBS:
SELECT DB_Name FROM Configuration;
LET LiczbaBaz = NoOfRows('DBS');
FOR i=0 TO $(LiczbaBaz)-1
          LET DB_Name = Peek('DB_Name', $(i), 'DBS');
          ACC_TYPE:
          SELECT
          '$(DB_Name)' AS DB_Name, 
          CASE WHEN Dim_1 = 1 THEN 'ER' WHEN Dim_1 = 2 THEN 'KP' WHEN Dim_1 = 3 THEN 'KH' END AS AccTypeName
          FROM CDN_$(DB_Name).CDN.Company WHERE Dim_1 = 3;
NEXT

Regards

Jonathan

Re: Inner Join (tricky one)

Sun, 18 Mar 2012 18:26:45 GMT

Hi Jonathan,

the only change you made is table name moved to SELECT statement... not working.

Regards,

Przemek

Inner Join (tricky one)

jvitantonio — Mon, 19 Mar 2012 09:31:35 GMT

What's the error your are getting?

Inner Join (tricky one)

CELAMBARASAN — Mon, 19 Mar 2012 09:41:24 GMT

DBS:
SELECT DB_Name FROM Configuration;


LET LiczbaBaz = NoOfRows('DBS');
FOR i=0 TO $(LiczbaBaz)-1
          LET DB_Name = Peek('DB_Name', $(i), 'DBS');

          ACC_TYPE:
          SELECT
          '$(DB_Name)' AS DB_Name, 
          CASE WHEN Dim_1 = 1 THEN 'ER' WHEN Dim_1 = 2 THEN 'KP' WHEN Dim_1 = 3 THEN 'KH' END AS AccTypeName
          FROM CDN_$(DB_Name).CDN.Company WHERE Dim_1 = 3;
NEXT i;

Inner Join(ACC_TYPE)

Load

Resident

DBS;

Drop Table DBS;

Celambarasan