from SQL to QV

[#1]

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

[martin59]

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;

[#3]

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

[martin59]

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;

[#5]

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);

[maxgro]

//

// test data (qvd)

//

table:

load * inline [

field

a

b

c

d

e

a

b

c

d

];

store table into table.qvd (qvd);

DROP Table table;

table1:

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

field1

a

b

c

d

e

f

g

h

i

l

];

store table1 into table1.qvd (qvd);

DROP Table table1;

table2:

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

field2

c

d

x

y

z

];

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);