SQL to Qlikview

[Anonymous]

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

[#2]

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

[arieidel]

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