Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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