Flag latest date opportunity

[paulyeo11]

Hi All

I have raw data below :-

Opportunity Closing Date	Opportunity Number	Stage of Opportunity
13-Aug-20	17	Detail Dicuussion( Prospect)
24-Aug-20	17	Quotation
25-Aug-20	17	Order Received
26-Aug-20	17	Order Received

Directory;
LOAD
[Opportunity Closing Date],
[Opportunity Starting Date],
[Opportunity Amount],
[Stage of Opportunity],
[Opportunity Number]
FROM
..\..\..\Dropbox\QV_RAW_ISDN\CRM_PM_.csv
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

May i know how to Flag the latest opportunity which is on 26 Aug 2020 using load script ?

Paul Yeo

[kamielrajaram]

Hi,

MaxClosingOp:

load max([Opportunity Closing Date]) as [Opportunity Closing Date] ,1 as [Last Opportunity Closing Flag] resident yourtable;

Left join(yourtable)

Load * resident MaxClosingOp; // this will join on [Opportunity Closing Date]

Drop Table MaxClosingOp;

[paulyeo11]

Hi Sir

Thank you for your sharing. I get error msg :-

Invalid expression
LOAD
[Opportunity Closing Date],
[Opportunity Starting Date],
[Opportunity Amount],
max([Opportunity Closing Date]) as [Opportunity Closing Date_],


[Stage of Opportunity],
[Opportunity Number]
FROM
..\..\..\Dropbox\QV_RAW_ISDN\CRM_PM_.csv
(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

Paul

[kamielrajaram · Accepted solution]

After you load your table load the below table. yourtablename will be the table name you created. The table below will be a new table that has your max date and flag.

MaxClosingOp:

load max([Opportunity Closing Date]) as [Opportunity Closing Date] ,1 as [Last Opportunity Closing Flag] resident yourtablename;

[Kushal_Chawda]

try below

Directory;

Data:
LOAD
[Opportunity Closing Date],
[Opportunity Starting Date],
[Opportunity Amount],
[Stage of Opportunity],
[Opportunity Number]
FROM
..\..\..\Dropbox\QV_RAW_ISDN\CRM_PM_.csv
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

left join(Data)

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

[paulyeo11 · Accepted solution]

Hi Kush

i use your solution , the Flag display mussing value.

Paul

[Kushal_Chawda]

It's because your Date fields are not in proper date format. set the below variable in main tab as per your data date format

SET DateFormat='DD-MMM-YY';

[paulyeo11]

Hi Sir

it work fine now.

Assume if i have duplicated row on the latest date , will qlik view know to pick up pick up which row ?

i have try , qlik view still pick up only one row , why  so smart ?

Paul 

[paulyeo11]

Hi Kush

i need to load mutiple company raw data , I try to make use of the same script for FOR script , now it is not working , any idea how to make it work ?

For Each i in 'PM','LE','DA','PW','SD'
Data:
LOAD
'$(i)' as SOURCE,
[Sales Person] AS Sales_Person,
[Opportunity Name] AS Opportunity_Name,
[Company Name] AS Company_Name,
[Contact Person] AS Contact_Person,
[Opportunity Closing Date] AS Closing_Date,
[Opportunity Closing Date],
[Opportunity Starting Date] AS Starting_Date,
[Opportunity Amount] AS Opp_Amount,
[Stage of Opportunity] AS Stage,
[Probability %] AS Probability,
[Opportunity Number] AS Opp_number
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],
1 as Latest_Date_Flag
resident Data;

[Kushal_Chawda]

see my answer here

https://community.qlik.com/t5/QlikView-Scripting/Case-910-How-to-add-partial-reload-with-FOR-Each-i-...