Case 936 How to Flag those date before today ?

[paulyeo11]

Hi All

i have raw data as below :-

DocDate

7/2/2019  12:00:00AM

8/2/2019  12:00:00AM

9/2/2019  12:00:00AM

10/2/2019  12:00:00AM

11/2/2019  12:00:00AM

12/19/2019  12:00:00AM

Input_Sales:

LOAD *,
Makedate(left(subfield(DocDate,'/',3),4),subfield(DocDate,'/',1),subfield(DocDate,'/',2)) as [Date_]
INLINE [
DocDate
7/2/2019 12:00:00AM
8/2/2019 12:00:00AM
9/2/2019 12:00:00AM
10/2/2019 12:00:00AM
11/2/2019 12:00:00AM
12/19/2019 12:00:00AM
];

Date_	Flag_Date_More_Then_Today
2/7/2019	Y
2/8/2019	Y
2/9/2019	Y
2/10/2019	N
2/11/2019	N
19/12/2019	N

Can some one advise me how to write the load script ?

Paul Yeo

[Taoufiq_Zarra]

@paulyeo11  all dates are  before today, can you elaborate why is Y and N in your output ?

[Taoufiq_Zarra]

@paulyeo11  with an other example :

load *,if([Date_]>today(),'Y','N') as Flag_Date_More_Then_Today;
LOAD *,
Makedate(left(subfield(DocDate,'/',3),4),subfield(DocDate,'/',1),subfield(DocDate,'/',2)) as [Date_]
INLINE [
DocDate
7/2/2020 12:00:00AM
8/2/2020 12:00:00AM
9/2/2020 12:00:00AM
10/2/2020 12:00:00AM
11/2/2020 12:00:00AM
12/19/2020 12:00:00AM
];

output:

[paulyeo11]

Hi Sir

I have apply your working load script to my actual QVW , it got error below :-

Field not found - <Makedate(left(subfield(DocDate,'/',3),4),subfield(DocDate,'/',1),subfield(DocDate,'/',2))>

Directory;
LOAD
DocDate,
if([Makedate(left(subfield(DocDate,'/',3),4),subfield(DocDate,'/',1),subfield(DocDate,'/',2))]>today(),'Y','N') as Flag_Date_More_Then_Today,
Makedate(left(subfield(DocDate,'/',3),4),subfield(DocDate,'/',1),subfield(DocDate,'/',2)) as [Date_]
FROM
[..\932 recode date\QUOT_SD_.csv]
(txt, codepage is 1252, embedded labels, delimiter is ' ', msq);

Paul

[Taoufiq_Zarra · Accepted solution]

@paulyeo11 

LOAD *,
if(Makedate(left(subfield(DocDate,'/',3),4),subfield(DocDate,'/',1),subfield(DocDate,'/',2))>today(),'Y','N') as Flag_Date_More_Then_Today,
Makedate(left(subfield(DocDate,'/',3),4),subfield(DocDate,'/',1),subfield(DocDate,'/',2)) as [Date_]
FROM
[..\932 recode date\QUOT_SD_.csv]
(txt, codepage is 1252, embedded labels, delimiter is ' ', msq);

[paulyeo11]

Hi Sir

Thank you very much.

I notice that you add * after Load
I aware that you try to load all field.
And it work fine

Paul