topic Replicate SQL queries to QlikView Script in QlikView

Replicate SQL queries to QlikView Script

shumailh — Sun, 28 Jun 2009 22:49:33 GMT

I am trying to replicate below SQL queries to my QlikView project but the result i am getting is not reconciling.. Can anyone please help? OR can we use left outer / keep on Load statment?


SQL Query 4
SELECT s2.BASE_SEGMENT_NBR, max(s2.[card_no]) as card_no, s2.Mid_14, max(s2.Replacement) as Replacement
FROM vwActiveCard_Step2 s2
left outer join vwActiveCard_Step3 s3 on s2.BASE_SEGMENT_NBR = s3.BASE_SEGMENT_NBR and s2.mid_14 = s3.mid_14
where s3.BASE_SEGMENT_NBR is null and s3.card_no is null group by s2.BASE_SEGMENT_NBR, s2.Mid_14
QV Script
V4:
LOAD Max(CARD_NO) as CARD_NO,'P' as V4, Max(LoadDate_V2) as LoadDate_V4, Max(CARD_NO) as CARD4, BASE_SEGMENT_NBR2 as BASE_SEGMENT_NBR4
, Mid_14_2 AS Mid_14_4, Max(Replacement2) AS Replacement4
RESIDENT V2
WHERE
not Exists(BASE_SEGMENT_NBR2, BASE_SEGMENT_NBR3) and
not Exists(Mid_14_2, Mid_14_3)
group by BASE_SEGMENT_NBR2, Mid_14_2;
SQL Query 5
SELECT S4.BASE_SEGMENT_NBR, S2.CARD_NO--, S2.[BlockCode1], S2.[BlockCode2], S2.[BlockCode3], S2.[CUR_BAL], S2.Replacement
FROM vwActiveCard_Step4 S4
LEFT OUTER JOIN vwActiveCard_Step2 S2 ON (S4.BASE_SEGMENT_NBR = S2.BASE_SEGMENT_NBR AND S4.Mid_14 = S2.Mid_14 AND S4.Replacement = S2.Replacement)
QV Script
V5:
LOAD CARD_NO,'P' as V5,LoadDate_V4 as LoadDate_V5,CARD_NO as CARD5, BASE_SEGMENT_NBR4 as BASE_SEGMENT_NBR5
, Mid_14_4 AS Mid_14_5, Replacement4 AS Replacement5
RESIDENT V4
WHERE Exists(BASE_SEGMENT_NBR2, BASE_SEGMENT_NBR4) and Exists(Mid_14_2, Mid_14_4) and Exists(Replacement2, Replacement4);

for more detail

Reference: http://community.qlik.com/forums/p/17422/68208.aspx#68208

Replicate SQL queries to QlikView Script

Anonymous — Mon, 29 Jun 2009 01:15:09 GMT

Shumail,

Query 4.
What bothers me in this SQL query is that you're joining on
s2.BASE_SEGMENT_NBR = s3.BASE_SEGMENT_NBR
and, at the same time using the condition
s3.BASE_SEGMENT_NBR is null
I'm not sure if it does any good. Does this SQL returns the same result?


SELECT
 s2.BASE_SEGMENT_NBR,
 max(s2.[card_no]) as card_no,
 s2.Mid_14,
 max(s2.Replacement) as Replacement
FROM vwActiveCard_Step2 s2
left outer join vwActiveCard_Step3 s3 on s2.mid_14 = s3.mid_14
where s3.BASE_SEGMENT_NBR is null
 and s3.card_no is null
group by s2.BASE_SEGMENT_NBR, s2.Mid_14

Anyway, I think you can use inner join here. This is how the LOAD may look like (that is, if I undestand the logic behind it, and your naming conventions):


V4:
LOAD
 Max(CARD_NO) as CARD_NO,
 'P' as V4,
 Max(LoadDate_V2) as LoadDate_V4,
 Max(CARD_NO) as CARD4,
 BASE_SEGMENT_NBR2 as BASE_SEGMENT_NBR4,
 Mid_14_2 AS Mid_14_4,
 Max(Replacement2) AS Replacement4
RESIDENT V2
GROUP BY BASE_SEGMENT_NBR2, Mid_14_2;
INNER JOIN (V4) LOAD
 BASE_SEGMENT_NBR3 as BASE_SEGMENT_NBR4,
 Mid_14_3 as Mid_14_4
RESIDENT V3
WHERE len(trim(CARD3))=0; // or is it CARD_NO here? You know better...

Query 5:
Looks OK, again, if I understand the logic, but you can use inner join as well:


V5:
 LOAD CARD_NO,
 'P' as V5,
 LoadDate_V4 as LoadDate_V5,
 CARD_NO as CARD5,
 BASE_SEGMENT_NBR4 as BASE_SEGMENT_NBR5,
 Mid_14_4 AS Mid_14_5,
 Replacement4 AS Replacement5
RESIDENT V4;
INNER JOIN (V4) LOAD
 BASE_SEGMENT_NBR2 as BASE_SEGMENT_NBR5,
 Mid_14_2 as Mid_14_4,
 Replacement2 as Replacement4
RESIDENT V2;

Replicate SQL queries to QlikView Script

shumailh — Mon, 29 Jun 2009 19:33:22 GMT

Hi Michael,

In query 4 I am doing cross join by using left outer join and getting all the rows which are not matching in view 3.

I am not getting the same rows as in sql query FOR Q4 & Q5. I have tried your join query in the below pattern but still i am not getting the same result as in SQL Server.


V4:
LOAD
 Max(CARD_NO) as CARD_NO,
 'P' as V4,
 Max(LoadDate_V2) as LoadDate_V4,
 Max(CARD_NO) as CARD4,
 BASE_SEGMENT_NBR2 as BASE_SEGMENT_NBR4,
 Mid_14_2 AS Mid_14_4,
 Max(Replacement2) AS Replacement4
RESIDENT V2
GROUP BY BASE_SEGMENT_NBR2, Mid_14_2;
left outer JOIN (V4) LOAD
 BASE_SEGMENT_NBR3 as BASE_SEGMENT_NBR4,
 Mid_14_3 as Mid_14_4
RESIDENT V3
WHERE not exists(CARD3, CARD2);

whereas query 5 is all dependent on the result of query 4 so i need to resolve query 4 first. can you please help that what would be the other possible way to do it so that my numbers has been reconcile.

Replicate SQL queries to QlikView Script

Anonymous — Mon, 29 Jun 2009 23:45:00 GMT

But you're using left outer join istead of inner join...
And, not exists(CARS3, CARD2) is not the same isnull(CARD3).
Try it the way I posted earlier.

Replicate SQL queries to QlikView Script

shumailh — Tue, 30 Jun 2009 00:43:06 GMT

I have tried ur aswell but i am not getting the same result as i have in SQL Server. :S

Can you review all the 5 queries from the link http://community.qlik.com/forums/p/17422/68208.aspx#68208............ May be we can optimize view# 4 & 5 and can rewrite the sql / qvw script for that.??

Replicate SQL queries to QlikView Script

Anonymous — Tue, 30 Jun 2009 03:40:31 GMT

Hi Shumail,
I think my queries should work correctly (if I understood the requirements), and can't see why the result is different. There is no way I can tell what is different without the direct access to your database, and trying myslef. Since it is not possible, you probably need an involved assistance from QlikTech consultants at your location. Sorry about that.
I'm thinking, if you're confident in the SQL queries results - why don't you use them as is in your QV script?

Replicate SQL queries to QlikView Script

shumailh — Tue, 30 Jun 2009 03:46:59 GMT

I have to develop this project standalone without any integration with SQL Server database. means i need to perform ETL from text file as we dont have SQL Server on Production due to company policies.

Thanks for support Michael, I will try to contact Qlikview consultant for more support.

Tell me is there anyway that I can upload my large sample QlikView file for the reconciliation?

Regards
Shumail

Replicate SQL queries to QlikView Script

Anonymous — Tue, 30 Jun 2009 03:49:40 GMT

Sorry for you - load from text file is slow, from my experience, and it's harder to implement more or less complex logic.

Replicate SQL queries to QlikView Script

shumailh — Tue, 30 Jun 2009 03:52:16 GMT

Tell me is there anyway that I can upload my large sample QlikView file for the reconciliation?

Replicate SQL queries to QlikView Script

shumailh — Tue, 30 Jun 2009 21:48:15 GMT

Hi Michael,

if you have seen the snap shot i have posted yesterday as above then the required result for query 4 is 6,333 records but i am getting 29,449 in qlikview already raised. I have tried to obtain the same result in sql which was not required but i got it successfully, below is the query FYR,



V4:
LOAD
 Max(CARD_NO) as CARD_NO, 'P' as V4, Max(LoadDate_V2) as LoadDate_V4, Max(CARD2) as CARD4,
 BASE_SEGMENT_NBR2 as BASE_SEGMENT_NBR4,
 Mid_14_2 AS Mid_14_4,
 Max(Replacement2) AS Replacement4
RESIDENT V2
GROUP BY BASE_SEGMENT_NBR2, Mid_14_2;
left JOIN (V4) LOAD
 BASE_SEGMENT_NBR3 as BASE_SEGMENT_NBR4,
 Mid_14_3 as Mid_14_4 //, COUNT(CARD_NO) AS NOOFCARD
RESIDENT V3
WHERE not exists(BASE_SEGMENT_NBR2, BASE_SEGMENT_NBR3) AND not exists(CARD2, CARD3)

select
 Max(s2.CARD_NO) as CARD_NO,
 'P' as V4,
 Max(s2.CARD_NO) as CARD4,
 s2.BASE_SEGMENT_NBR as BASE_SEGMENT_NBR,
 s2.Mid_14 AS Mid_14_4,
 Max(s2.Replacement) AS Replacement4
from vwActiveCard_Step2 s2
GROUP BY s2.BASE_SEGMENT_NBR, s2.Mid_14;

Still i couldnt figure out how to replicate the required sql statement in qlikview as i raised earlier

Replicate SQL queries to QlikView Script

Anonymous — Tue, 30 Jun 2009 23:41:35 GMT

Hi Shumail,
Are you getting the correct result in V4 now? The logic in the load script and in SQL queries looks different. Maybe "replicate" should not be the puropse after all?
(I'll try to take a closer look when I get time. It's not a 15-min answer...)

Replicate SQL queries to QlikView Script

shumailh — Wed, 01 Jul 2009 00:03:59 GMT

Hi Michael,

still not

Below are the queries which i have created. My objective is to get 6,333 records from V4 but i am getting 29,449 records. I have created another query from which i am getting 23,116 records. If you subtract both number of records then i can get 6,333 records which i require.

Can you please help me in the below query that how can i subtract V4 and V6 and get 6,333 records? I tried not exists function but its not working


V4:
LOAD
 Max(CARD_NO) as CARD_NO,
 'P' as V4,
 Max(LoadDate_V2) as LoadDate_V4,
 Max(CARD2) as CARD4,
 BASE_SEGMENT_NBR2 as BASE_SEGMENT_NBR4,
 Mid_14_2 AS Mid_14_4,
 Max(Replacement2) AS Replacement4
RESIDENT V2
GROUP BY BASE_SEGMENT_NBR2, Mid_14_2;
left JOIN (V4) LOAD
 BASE_SEGMENT_NBR3 as BASE_SEGMENT_NBR4,
 Mid_14_3 as Mid_14_4
RESIDENT V3
WHERE not exists(BASE_SEGMENT_NBR2, BASE_SEGMENT_NBR3) AND not exists(CARD2, CARD3);

V6:
LOAD
 Max(CARD_NO) as CARD_NO,
 'P' as V6,
 Max(LoadDate_V2) as LoadDate_V6,
 Max(CARD2) as CARD6,
 BASE_SEGMENT_NBR2 as BASE_SEGMENT_NBR6,
 Mid_14_2 AS Mid_14_6,
 Max(Replacement2) AS Replacement6
RESIDENT V2
GROUP BY BASE_SEGMENT_NBR2, Mid_14_2;
inner JOIN (V6) LOAD
 BASE_SEGMENT_NBR3 as BASE_SEGMENT_NBR6,
 Mid_14_3 as Mid_14_6
RESIDENT V3;

Replicate SQL queries to QlikView Script

Anonymous — Wed, 01 Jul 2009 02:49:04 GMT

This is interesting. It must be "not exists". The quesion is - what the correct parameters are. What is the primary ID of the record? Is it BASE_SEGMENT_NBRn? If yes:


V41:
LOAD
...
RESIDENT V4
WHERE not exists(BASE_SEGMENT_NBR6, BASE_SEGMENT_NBR4)

Replicate SQL queries to QlikView Script

shumailh — Wed, 01 Jul 2009 12:06:36 GMT

Hey Michael,

It's done, I had broken query 4 in 2 queries as follows:


V6:
LOAD
 Max(CARD_NO) as CARD_NO,
 'P' as V4,
 Max(LoadDate_V2) as LoadDate_V6,
 Max(CARD2) as CARD6,
 BASE_SEGMENT_NBR2 as BASE_SEGMENT_NBR6,
 Mid_14_2 AS Mid_14_6,
 Max(Replacement2) AS Replacement6
RESIDENT V2
GROUP BY BASE_SEGMENT_NBR2, Mid_14_2;
LEFT JOIN (V4) LOAD
 BASE_SEGMENT_NBR3 as BASE_SEGMENT_NBR6,
 Mid_14_3 as Mid_14_6
RESIDENT V3;

V4:
LOAD CARD_NO,'P' as V4,LoadDate_V6 as LoadDate_V4, CARD_NO as CARD4, BASE_SEGMENT_NBR6 AS BASE_SEGMENT_NBR4
, Mid_14_6 as Mid_14_4, Replacement6 as Replacement4
RESIDENT V6
WHERE NOT Exists(Mid_14_3, Mid_14_6);

drop table V6;

I still not able to learn the way to write load statement perfectly. Neways whatever it is but i am getting my result.

Thanks Michael for the great support.

Regards,
Shumail Hussain

Replicate SQL queries to QlikView Script

Anonymous — Wed, 01 Jul 2009 23:02:59 GMT

Great!
(Sometimes there is no "perfect" solution, especailly when you don't have access to the database.)