Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Inner Join (tricky one)

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

1 Solution

Accepted Solutions
CELAMBARASAN
Partner - Champion
Partner - Champion

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

View solution in original post

4 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

Hi Jonathan,

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

Regards,

Przemek

jvitantonio
Luminary Alumni
Luminary Alumni

What's the error your are getting?

CELAMBARASAN
Partner - Champion
Partner - Champion

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