Case 910 How to add partial reload with FOR Each i and Next i; ?

[paulyeo11]

Hi All

I have below 2 raw data , i need to flag the latest date for field = [Opportunity Closing Date]

Below is SOURCE = SD

Company Name	Opportunity Closing Date
A.E. ENGINEERS(S) PTE LTD	5-Aug-20
A.E. ENGINEERS(S) PTE LTD	5-Sep-20

Below is SOURCE = PM

Company Name	Opportunity Closing Date
ETUAN MECHATRONIC PTE LTD	26-Aug-20
ETUAN MECHATRONIC PTE LTD	25-Aug-20

i Use below script which working fine no error , But Part 2 of the script is not working :-

// Part (1)

For Each i in 'PM','SD'
Data:
LOAD
'$(i)' as SOURCE,
[Opportunity Closing Date] AS Closing_Date,
[Opportunity Closing Date],
[Opportunity Amount] AS Opp_Amount
FROM
$(vRAWPath)CRM_$(i)_.CSV
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

// Part (2)

left join(Data)
load
date(max([Opportunity Closing Date])) as [Opportunity Closing Date],
'$(i)' as SOURCE,
1 as Latest_Date_Flag
resident Data;
NEXT i;

My issue is it only work for the first loop. May i know how to make it work for second loop ? How to flag Closing Date = 26 Aug 20

Latest_Date_Flag	SOURCE	Closing_Date
-	SD	5-Aug-20
1	SD	5-Sep-20
-	PM	13-Aug-20
-	PM	24-Aug-20
-	PM	25-Aug-20
1	PM	26-Aug-20

Paul Yeo

[Kushal_Chawda · Accepted solution]

Problem is you are doing join within loop. see the below for explanation

https://community.qlik.com/t5/New-to-QlikView/left-join-in-a-loop/td-p/629005

So remove your left join code from loop and place it outside the loop

For Each i in 'PM','SD'
Data:
LOAD
'$(i)' as SOURCE,
[Opportunity Closing Date] AS Closing_Date,
[Opportunity Closing Date],
[Opportunity Amount] AS Opp_Amount
FROM
$(vRAWPath)CRM_$(i)_.CSV
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

NEXT i;

left join(Data)
load
date(max([Opportunity Closing Date])) as [Opportunity Closing Date],
SOURCE,
1 as Latest_Date_Flag
resident Data

group by SOURCE;

[paulyeo11]

Hi Kush

Thank you very much it work fine now.

Instead of Flag can you pls help me to delete those raw and on;y keep the latest date row. ( I only need those row that are Flag ) , as i actual data it does not have duplicate row . 

Ref to below table i need to flag 26 Aug and 11 Sept , where 11 sept row only have one date i also need to flag. 

Opportunity Number	Opportunity Closing Date	Opportunity Name
17	26-Aug-20	HP SINGAPORE DDL PROJECT
17	25-Aug-20	HP SINGAPORE DDL PROJECT
17	24-Aug-20	HP SINGAPORE DDL PROJECT
17	13-Aug-20	HP SINGAPORE DDL PROJECT
16	11-Sep-20	Corning Machine Project

Paul