Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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

Tags (2)
1 Solution

Accepted Solutions

Inner Join (tricky one)

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

4 Replies
MVP
MVP

Re: Inner Join (tricky one)

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

Re: Inner Join (tricky one)

Hi Jonathan,

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

Regards,

Przemek

Luminary
Luminary

Inner Join (tricky one)

What's the error your are getting?

Inner Join (tricky one)

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