topic Re: SQL in script, metadata, overtime trend observation in QlikView

SQL in script, metadata, overtime trend observation

Wed, 25 Nov 2020 16:16:04 GMT

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

date	total_row	number_of_rows_of_category1	number_of_rows_of_category2	number_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!

Re: SQL in script, metadata, overtime trend observation

Gysbert_Wassenaar — Tue, 06 Dec 2016 09:11:35 GMT

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.

Re: SQL in script, metadata, overtime trend observation

Tue, 06 Dec 2016 09:57:17 GMT

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

Re: SQL in script, metadata, overtime trend observation

Gysbert_Wassenaar — Tue, 06 Dec 2016 10:01:50 GMT

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