topic Re: Issue with Makedate function in QlikView

Issue with Makedate function

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hello

I have a test data as follows:

DateTest

1/05/2018 03:05:45 a.m

2/05/2018 03:07:55 a.m

3/05/2018 03:30:25 a.m

4/05/2018 03:30:25 a.m

5/05/2018 03:30:25 a.m

6/05/2018 03:30:25 a.m

7/05/2018 03:30:25 a.m

8/05/2018 03:30:25 a.m

9/05/2018 03:30:25 a.m

10/05/2018 03:30:25 a.m

11/05/2018 03:30:25 a.m

12/05/2018 03:30:25 a.m

13/05/2018 03:30:25 a.m

14/05/2018 03:30:25 a.m

15/05/2018 03:30:25 a.m

16/05/2018 03:30:25 a.m

17/05/2018 03:30:25 a.m

18/05/2018 03:30:25 a.m

I am using a code:

Test:

LOAD DateTest,

makedate(Date#(year(DateTest),month(DateTest)),'MM-YYYY') as RevisedDate

FROM

(ooxml, embedded labels, table is Sheet1);

It does not return any output. I also tried running the function without Date#. The same thing happened.

Got a blank listbox in both cases.

Please help.

Regards

Mahendra

Re: Issue with Makedate function

duncanblaine — Tue, 01 May 2018 05:45:54 GMT

Hi Mahendra
The Date# function won't automatically know what format your date is in
In this example, I would keep it simple and use SubField to extract the first part of the date, ignoring the time part.
I've used a preceding load to create the RevisedDate just to reduce cluttered code.

This script should work for you if you copy and paste:

Test:

LOAD DateTest, Date(makeDate(Year(DateTestAsDate),Month(DateTestAsDate)),'MM-YYYY') as RevisedDate;

LOAD

DateTest,

Date(Date#(SubField(DateTest,' '),'D/MM/YYYY')) as DateTestAsDate

FROM

(ooxml, embedded labels, table is Sheet1);

Re: Issue with Makedate function

duncanblaine — Tue, 01 May 2018 06:05:33 GMT

An alternative to my previous reply, that might work better with your script and retain the time element in your date..

The date format is unusual in that it has 'a.m' not 'am'. However as you only really need to 'a' or 'p', you can remove the '.m' using Replace().
Then your date will be a recognised format 'D/MM/YYYY hh:mm:ss t'.
You still need to pass that to Date# as follows:

Date#(Replace(DateTest,'.m',''),'D/MM/YYYY hh:mm:ss t')

Re: Issue with Makedate function

Anonymous — Tue, 01 May 2018 07:05:00 GMT

Hi Mahendra

Try this

Date(TimeStamp#(Replace(DateTest,'.m','m'),'D/MM/YYYY hh:mm:ss tt'),'MM-YYYY') as RevisedDate

Re: Issue with Makedate function

Anil_Babu_Samineni — Tue, 01 May 2018 07:25:50 GMT

PFA

Load *, Num(Month(Left(DateTest,10))) & '-' & Year(Left(DateTest,10)) as Output From Source ...

Re: Issue with Makedate function

Anonymous — Tue, 01 May 2018 07:36:47 GMT

Thanks to all for such prompt response.

However I apologize for not describing my requirement properly.

eg: 10/05/2018 03:30:25 a.m.

I need it to be represented as 5-May-2018 or 5-May-18

Regards

Mahendra

Re: Issue with Makedate function

Anil_Babu_Samineni — Tue, 01 May 2018 07:41:48 GMT

Like this?

Date(Left(DateTest,10),'DD') & '-' & Month(Left(DateTest,10)) & '-' & Year(Left(DateTest,10)) as Output

Date(Left(DateTest,10),'DD-MMM-YYYY') as Output

Re: Issue with Makedate function

Anonymous — Tue, 01 May 2018 07:42:05 GMT

PFA

Re: Issue with Makedate function

sasiparupudi1 — Tue, 01 May 2018 09:14:01 GMT

Try

Date (Date#(Subfield(DateTest,' ',1),'DD/MM/YYYY'),'DD-MMM-YYYY')

Re: Issue with Makedate function

Anonymous — Tue, 01 May 2018 10:36:25 GMT

I am trying to run a script:

sub TraceRowCount (SourceTable)

//Writes the number of rows in SourceTable

//to the Script Execution Progress window

if '$(SourceTable)' <> ''

let vNoOfRows = NoOfRows('$(SourceTable)');

trace >>> Number of rows in $(SourceTable):$(vNoOfRows);

let vNoOfRows=Null();

Else

trace >>> No table name specified;

End if

End sub

It is throwing an error as follows:

Re: Issue with Makedate function

duncanblaine — Tue, 01 May 2018 10:42:27 GMT

You're missing a 'THEN' here:
if '$(SourceTable)' <> '' THEN

Re: Issue with Makedate function

Anonymous — Tue, 01 May 2018 10:43:14 GMT

Anil

None of them are working

Regards

Mahendra

Re: Issue with Makedate function

Anonymous — Tue, 01 May 2018 10:47:40 GMT

Sashidhar

That really worked.

Thanks a lot for your help.

Also can you please let me know if conditional statements can be used in textbox objects?

If you can provide a simple example of it