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]

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

[shumailh]

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;

[Anonymous]

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)

[shumailh]

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

[Anonymous]

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