topic Re: from SQL to QV in QlikView

from SQL to QV

Fri, 13 Aug 2010 15:35:58 GMT

Hello,

how to develop in qV this kind of sql code:

select c.field1 ,b.field2

from table1 b,Table2 c

where b.field2 =c.field1

and c.field1 in

(select a.field from table a group by a.field having count(*)>1 )

order by c.field1

with ur

Re. :from SQL to QV

martin59 — Fri, 13 Aug 2010 15:44:10 GMT

Hi,

You have to do something like that:

Table:
LOAD DISTINCT field;
SQL SELECT field
FROM table
GROUP BY field
HAVING count(*)>1;

Table2:
LEFT KEEP (Table)
LOAD DISTINCT field1 as field;
SQL SELECT field1
FROM table2;
INNER JOIN (Table2)
LOAD DISTINCT field2 as field;
SQL SELECT field2
FROM table1;
DROP TABLE Table;

Re. :from SQL to QV

Fri, 13 Aug 2010 15:47:56 GMT

MA data source (table, table1, table 2) are QVD i cant use Having 😞

Re. :Re: Re. :from SQL to QV

martin59 — Fri, 13 Aug 2010 15:51:41 GMT

Is that helps you ?

<blockquote><pre>tmpTable:

LOAD field, count(*) as count;
SQL SELECT field
FROM table
GROUP BY field;
Table:
LOAD field
RESIDENT tmpTable
WHERE count > 1;
DROP TABLE tmpTable;

Re: Re. :from SQL to QV

Wed, 29 Jul 2015 02:48:09 GMT

It takes a little bit of change in thinking when you come from SQL world. Qlikview will join only on same column names.

I am thinking you want more columns from the 2 tabes other than the Joining one. You can add them too. There may be syntax error in my code but you get the idea.

Main:

LOAD field as field1

WHERE COUNTA >1;

LOAD id, count(id) FROM TABLE_QVD (qvd);

Inner JOIN (Main)

LOAD field1 from TABLE2_QVD (qvd);

inner Join (MAIN)

LOAD field2 as field1 from TABLE3_QVD (qvd);

Re: from SQL to QV

maxgro — Wed, 29 Jul 2015 05:48:53 GMT

// test data (qvd)

table:

load * inline [

field

];

store table into table.qvd (qvd);

DROP Table table;

table1:

load *, rand()*10 as exp1 inline [

field1

];

store table1 into table1.qvd (qvd);

DROP Table table1;

table2:

load *, rand()*100 as exp2 inline [

field2

];

store table2 into table2.qvd (qvd);

DROP Table table2;

// end test data (qvd)

// start of your Sql code in Qlik using test data

table:

load field where countfield > 1; // having > 1

load field, count(field) as countfield from table.qvd (qvd) group by field;

table1:

load field1

from table1.qvd (qvd)

where exists (field, field1); // c.field1 in (select .......)

Inner Join (table1) // join table1 and table2

load

field2 as field1, // rename field2 to join with field1

field2

from table2.qvd (qvd);