Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

SQL, combine multiple SQL query result tables

$(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!!

1 Solution

Accepted Solutions
adamdavi3s
Master
Master

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);

View solution in original post

6 Replies
adamdavi3s
Master
Master

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);

View solution in original post

payalgosar
Creator II
Creator II

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);
asgardd2
Creator III
Creator III

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);

Not applicable
Author

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!!

Not applicable
Author

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?

Not applicable
Author

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?