Using SQL query on QVD File

[shumailh]

Can we perform SQL query on a QVD source file

[#2]

Hello Shumail,

When loading a QVD file you can use all QlikView script functions to perform a query. You can't use SQL statements, but QlikView script functions should be sufficient enough. Which SQL functions do you need to perform?

regards Mark

[shumailh]

Hi Mark,

Actually I am using a union to concatenate the output of 2 SQL query. Also both the queries are complex query using inline query and where clause having multiple condition. below is a query written in the Oracle. kindly let us know how to achive the same in Qlik.

[Code]

select card_number,CURRENT_BALANCE,base_segment_number from
(select base_segment_number,CURRENT_BALANCE,substr(card_number,1,14) Mid_14,max(substr(card_number,15,1)) replacements,max(card_number) card_number from
(select card_number, BLOCK_CODE_1,BLOCK_CODE_2,BLOCK_CODE_3,CURRENT_BALANCE,base_segment_number from
(Select substr(a.CARD_NUMBER,4) card_number, BLOCK_CODE_1,BLOCK_CODE_2,BLOCK_CODE_3,CURRENT_BALANCE,substr(b.base_segment_number,4) base_segment_number from
(select * from ccm_dbf_chtxn_v0 where batch_id='010409') a, (select * from ccm_dbf_phtxn_v0 where batch_id='010409') b
where a.card_number=b.card_number and a.company ='BNK' and NOT (NVL(block_code_1,'$') in ('R','F','L') OR NVL(block_code_2,'$') in ('R','F','L') OR NVL(block_code_3,'$') in ('R','F','L')))
where (((NVL(block_code_1,'$') in ('U','V','W','D','G','X') OR NVL(block_code_2,'$') in ('U','V','W','D','G','X') OR NVL(block_code_3,'$') in ('U','V','W','D','G','X'))
and current_balance<>0)
or not (NVL(block_code_1,'$') in ('U','V','W','D','G','X') OR NVL(block_code_2,'$') in ('U','V','W','D','G','X') OR NVL(block_code_3,'$') in ('U','V','W','D','G','X')))
union
select all_lf.card_number,BLOCK_CODE_1,BLOCK_CODE_2,BLOCK_CODE_3,all_lf.CURRENT_BALANCE,base_segment_number from
(select base_segment_number,CURRENT_BALANCE,substr(card_number,1,14) Mid_14,max(substr(card_number,15,1)) replacements,max(card_number) card_number from
(select substr(a.CARD_NUMBER,4) CARD_NUMBER, BLOCK_CODE_1,BLOCK_CODE_2,BLOCK_CODE_3,CURRENT_BALANCE,substr(base_segment_number,4) base_segment_number,a.company from
(select * from ccm_dbf_chtxn_v0 where batch_id='010409') a, (select * from ccm_dbf_phtxn_v0 where batch_id='010409') b
where a.card_number = b.card_number and NOT (NVL(block_code_1,'$') in ('R') OR NVL(block_code_2,'$') in ('R') OR NVL(block_code_3,'$') in ('R')))
where (NVL(block_code_1,'$') in ('F','L') OR NVL(block_code_2,'$') in ('F','L') OR NVL(block_code_3,'$') in ('F','L')) and current_balance!=0 and company='BNK'
group by base_segment_number,substr(card_number,1,14),CURRENT_BALANCE) ALL_LF,(select * from ccm_dbf_chtxn_v0 where batch_id='010409') ch
where '000'||all_lf.card_number=ch.card_number)
group by base_segment_number,substr(card_number,1,14),CURRENT_BALANCE)

[\Code]

[#4]

Hello Shumail,

What is the reason that you don't perform this query directly on data source? Union (ALL) is possible by just adding two different query's to the same table. As far as I know QlikView LOAD does not support Inline query's, but you can use Mapping tables for this. Good luck!

regards Mark

[shumailh]

Thanks Mark!

But what i am planning to do is to develop an end to end solution on QlikVIew which is independent of any database. I dont want to have a dependency on SQL Server / ORACLE or any other database tool. what i have found that we can perform ETL on QlikView and I am currently loading and transforming data on Qlik view. but when i wanted to perform nested and sql queries i got stucked and couldn't go forward to extract meaningfull data from the QVDs. If this inline query option is there in the qlikview then i would successfull complete this project

As you already mentioned that LOAD command is not capable to support inline queries or where clause etc, it means that i need to compromise on the ETL module which i have designed on QlikView and I have to rework this ETL activity on any other RDBMS tool and design the desired nested & sql queries and integrate it with QlikView.

Regards
Shumail Hussain

[#6]

Although you cannot do subqueries in the load statement, I know that you can achieve anything you can do with them buy doing multiple Load, Join, Keep, and Group by statements. It is just a matter of transforming the data one step at a time. I would start with the most granular data, and analyze it more with each load statement.

[rbecher]

A good way is to have a QVD staging area where you can collect data from different databases (SQL Server, Oracle) and then load into your QV apps.

You can have WHERE clauses in LOAD statements from QVD files and also can join between those different files what would not be possible (or much more complex) to do on database level.

Hope this helps after months 😉

- Ralf