Replicate SQL queries to QlikView Script

[shumailh]

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

[Anonymous · Accepted solution]

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;

[shumailh]

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.

[Anonymous]

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.

[shumailh]

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.??

[Anonymous]

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?

[shumailh]

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

[Anonymous]

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

[shumailh]

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

[shumailh]

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