Search code examples
sql-serverbusiness-intelligenceqlikview

SQL equivalent query in Qlikview?

In SQL we can write query like:

Select field1,field2,field3,field4,field5,field6,field7
from table1 t1,table2 t2,table3 t3
where t1.field1 = t3.field3 and
      t2.field2 = 'USD'

In Qlikview, I have created QVD's for 6 tables, now I want to create a single QVD of these 6 QVD's. Unfortunately these tables don't contain primary keys. So I cant use join. I have tried like this also:

fact:
load * 
from 
[D:\path\fact*.qvd](qvd);
//To store all qvd's into one qvd.
store fact into [D:\path\facttable.qvd];

This query creates a facttable but only with 2 columns, these columns are of first fact table. Diagram shows it much clear:

enter image description here

As it internally gives the name of all the facts table with fact, fact-1, fact-2 and so on and I have written the query store fact into [D:\path\facttable.qvd]; and in this diagram fact table contains only two columns so it creates the fact table with two columns only.

Please let me know the solution that how can we write this query in Qlikview or how can we create a fact table using all the QVDS?

Thanks in advance.

Solution

  • Since every qvd contains different field names it will create several tables with synthetic keys when you load *.

    You can use Concatenate Load to stack each qvd onto one fact table. One simple example would be to first create a Fact table by:

    Fact:
Load * INLINE [
dummyField
];

    Now you can concatenate the qvd's onto that Fact table:

    concatenate(Fact)
load * 
from 
[D:\path\fact*.qvd](qvd);

//To store all qvd's into one qvd.

store Fact into [D:\path\facttable.qvd];
//drop the dummy field.
drop field dummyField;