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 in script, metadata, overtime trend observation

     Hi All,

I have some tricky project that I have to do.

it is presenting the trend of something over time.

I have a big dataset and I want to present the status of the dataset.

so I have SQL query that looks like

select count(*) from dataset.

select count(*) from dataset where ~~~

select count(*) from dataset where ~~~ and ~~~

each of these queries gives a snapshot of how the dataset looks like.

but I have to present a trend of the snapshot. so I will run the calculation each day and I hope to add one additional row per day

datetotal_rownumber_of_rows_of_category1number_of_rows_of_category2number_of_rows_of_category3








20131201

20131202

something that looks like this

so I was planning to take these following steps

1. connect the SQL database to QVD Creator

2. somehow save all the result of calculations into a variable

3. make the variable each column of the data table

4. do an increment load

this is the vague idea that I had in my mind but this does not look promising for few reasons.

1. SQL query does not seem to be running well

2. eventhough I successfully get that result from the query, it will be in a form of table. it will be just 1x1 table but still it is a table so I am wonder how I will be able to put that into a number format so that the variable will be holding a number value.

3. how do I create a table with each variable as a column

please give me an advice on any of the process. I want to make progress after getting some advice.

Thank you!

3 Replies
Gysbert_Wassenaar

You can concatenate the results of each query to get a table with all the results:

ResultTable:

select count(*), 'category1' as category, sysdate() as date from dataset;

CONCATENATE(ResultTable)

select count(*),  'category2' as category, sysdate() as date from dataset where ~~~ ;

CONCATENATE(ResultTable)

select count(*),  'category3' as category, sysdate() as date from dataset where ~~~ and ~~~ ;

You can use the field category that's created as a dimension in for example a pivot table.


talk is cheap, supply exceeds demand
Not applicable
Author

Thank you gysbert!

outer join worked well for me and I got the table in the format that I wanted.

I have a follow up questions.

do you think I can create another column of category 4 by getting the value of category 1 - category 2??

Gysbert_Wassenaar

If you used a join instead of concatenate then that's possible.


talk is cheap, supply exceeds demand