topic Re: SQL, combine multiple SQL query result tables in QlikView

SQL, combine multiple SQL query result tables

Wed, 25 Nov 2020 16:16:04 GMT

$(QVDatabaseTable):

//NoOf_Rows:

LOAD * ,

date(today(),'YYYY-DD-MM') as date;

SQL SELECT count(*) as NoOf_Rows

FROM $(DatabaseName).$(DatabaseUser).$(DatabaseTable);

//$(INCREMENTAL_EXP);

//NoOf_Builds:

LOAD * ,

date(today(),'YYYY-DD-MM') as date;

SQL SELECT count (distinct category) as NoOf_Category

FROM $(DatabaseName).$(DatabaseUser).$(DatabaseTable);

//$(INCREMENTAL_EXP);

Store $(QVDatabaseTable) into $(vQVDFileName)(qvd);

Drop Table $(QVDatabaseTable);

what I was trying to do here is to combine the result of the first and second query result table by date.

so the first table will have a table with column name date and NoOf_Rows and the second table will have

NoOfCategory and date. I want to have a table with date / NoOfRows / NoOfCategory.

Please help!!

Re: SQL, combine multiple SQL query result tables

adamdavi3s — Tue, 06 Dec 2016 08:48:52 GMT

You should be able to do this with a join, I would suggest outer to make sure you get all records:

$(QVDatabaseTable):
//NoOf_Rows:
LOAD * ,
  date(today(),'YYYY-DD-MM') as date;

SQL SELECT count(*) as NoOf_Rows
FROM $(DatabaseName).$(DatabaseUser).$(DatabaseTable);

//$(INCREMENTAL_EXP);

OUTER JOIN
//NoOf_Builds:
LOAD * ,
  date(today(),'YYYY-DD-MM') as date;

SQL SELECT count (distinct category) as NoOf_Category
FROM $(DatabaseName).$(DatabaseUser).$(DatabaseTable);

//$(INCREMENTAL_EXP);
Store $(QVDatabaseTable) into $(vQVDFileName)(qvd);
Drop Table $(QVDatabaseTable);

or simply:

LOAD * ,
  date(today(),'YYYY-DD-MM') as date;

SQL SELECT count(*) as NoOf_Rows,  count (distinct category) as NoOf_Category
FROM $(DatabaseName).$(DatabaseUser).$(DatabaseTable);

//$(INCREMENTAL_EXP);

Re: SQL, combine multiple SQL query result tables

Anonymous — Tue, 06 Dec 2016 08:54:50 GMT

Hi David,

Use Concatenate keyword before load statement of second table.

E.g.:

$(QVDatabaseTable):

LOAD * ,

date(today(),'YYYY-DD-MM') as date;

SQL SELECT count(*) as NoOf_Rows

FROM $(DatabaseName).$(DatabaseUser).$(DatabaseTable);

//$(INCREMENTAL_EXP);

Concatenate ($(QVDatabaseTable))

LOAD * ,

date(today(),'YYYY-DD-MM') as date;

SQL SELECT count (distinct category) as NoOf_Category

FROM $(DatabaseName).$(DatabaseUser).$(DatabaseTable);

Re: SQL, combine multiple SQL query result tables

asgardd2 — Tue, 06 Dec 2016 08:57:44 GMT

Best way is write one SQL query to the database.

Select

count(*) as NoOf_Rows,

count (distinct category) as NoOf_Category,

date(today(),'YYYY-DD-MM') as date

FROM $(DatabaseName).$(DatabaseUser).$(DatabaseTable);

Re: SQL, combine multiple SQL query result tables

Tue, 06 Dec 2016 09:14:31 GMT

Thank you Anton,

I tried this but this did not work.

ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: 'today' is not a recognized built-in function name.

this is the error message.

I will try the above recommened ways and let you know what worked

THank you!!

Re: SQL, combine multiple SQL query result tables

Tue, 06 Dec 2016 09:21:21 GMT

This worked just the way I want! it correctly concatenated the tables!

Thank you very much

Can I ask you a follow up question?

I want this to update each day. so I will set this up to run everyday but what I need to figure out is to get this number each day and save them. adding one row per day.

how can I do that?

Re: SQL, combine multiple SQL query result tables

Tue, 06 Dec 2016 10:00:39 GMT

also one other question!!

is it possible to make another column by adding or subtracting other two columns?

for example, let's say by Outer Joining I got three columns col1, col2, col3

Can I make col4 by col1 - col2?