topic Re: Case 936 How to Flag those date before today ? in QlikView

Case 936 How to Flag those date before today ?

paulyeo11 — Sat, 19 Sep 2020 08:36:22 GMT

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

Re: Case 936 How to Flag those date before today ?

Taoufiq_Zarra — Sat, 19 Sep 2020 08:44:46 GMT

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

Re: Case 936 How to Flag those date before today ?

Taoufiq_Zarra — Sat, 19 Sep 2020 08:48:59 GMT

@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:

Re: Case 936 How to Flag those date before today ?

paulyeo11 — Sat, 19 Sep 2020 09:04:34 GMT

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

Re: Case 936 How to Flag those date before today ?

Taoufiq_Zarra — Sat, 19 Sep 2020 09:17:40 GMT

@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);

Re: Case 936 How to Flag those date before today ?

paulyeo11 — Sun, 20 Sep 2020 04:50:05 GMT

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