topic Re: SQL to Qlikview in QlikView

SQL to Qlikview

Anonymous — Tue, 30 Jul 2013 12:17:44 GMT

Hi all,

I would like to convert the below SQL query into Qlikview. Please help me converting the below query. Its little urgent. I tried to convert the one I have marked in bold but getting wrong result. So help me in converting that part in Qlikview.

SELECT tb1.field1, 'AR' AS field2,

tb2.field3,

SUM(tb1.field4 - tb1.field5) local_amount

FROM table1 tb1

JOIN table2 tb2

ON tb2.field1 = tb1.field6

AND tb2.field2 = tb1.field7

LEFT JOIN table3 tb3

ON tb3.field1 = tb1.field6

AND tb3.field2 = tb1.field8

WHERE (tb1.field9 != 'xxx' AND tb1.field10 IS NULL)

AND tb1.field11 = 'ccc'

AND tb1.field12 != 'cancelled'

AND tb1.field12 != 'paid'

AND NOT EXISTS (SELECT 1

FROM table4 tb4

WHERE tb4.field1 = tb1.field6

AND tb4.field2 = tb1.field7

AND tb4.field3 = tb1.field8

AND tb4.field4 = tb1.field11

AND tb4.field5 = '3000')

AND tb1.field6 IN ('1','2','3')

GROUP BY tb1.field7, tb2.field3

Thank you,

Leni

Re: SQL to Qlikview

Tue, 30 Jul 2013 13:48:27 GMT

Please try this
Load *,tb4.field1&'#'&tb4.field2&'#'&tb4.field3&'#'&tb4.field4 as Key_Field FROM table4 tb4
 where tb4.field5 <> '3000';

Load *,tb1.field1&'#'&tb1.field2&'#'&tb1.field3&'#'&tb1.field4 as Key_Field
where not exists(Key_Field,Key_Field);
SELECT tb1.field1
,tb1.field2,tb1.field3,tb1.field4, 'AR' AS field2,
       tb2.field3,
       SUM(tb1.field4 - tb1.field5) local_amount
  FROM table1 tb1
  JOIN table2 tb2
    ON tb2.field1 = tb1.field6
   AND tb2.field2 = tb1.field7
  LEFT JOIN table3 tb3
    ON tb3.field1 = tb1.field6
   AND tb3.field2 = tb1.field8
 WHERE (tb1.field9 != 'xxx' AND tb1.field10 IS NULL)
   AND tb1.field11 = 'ccc'
   AND tb1.field12 != 'cancelled'
   AND tb1.field12 != 'paid'
   AND tb1.field6 IN ('1','2','3')
GROUP BY tb1.field7, tb2.field3

Re: SQL to Qlikview

arieidel — Wed, 20 Nov 2013 19:37:58 GMT

Hi,

You should really create a VIEW object in your database. Then, in QV Script you have only to LOAD from that view. You keep the QV script simple and clean, leaving the complexity to the database.

Hope it helps!

Ariel