Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

markgraham123
Not applicable

0 value in date

Hi All,

I have a date filed which is in the format of YYYYMMDD (Which has 0 value - on a purpose)

I'm converting it to MM/DD/YYYY by the statement:

MakeDate(Left(Test,4),Mid(Test,5,2),Right(Test,2))

But when i want to retain 0 value too, it i snot fetching it.

Can someone please look in to the application attached??

Tags (1)
1 Solution

Accepted Solutions
sunny_talwar
Not applicable

Re: 0 value in date

Try this:

Table1:

LOAD *,

  If(IsNull([Test Temp]),0,[Test Temp]) as [Test Final];

LOAD *,

  If([Test]='0', 0, Date(MakeDate(Left(Test,4),Mid(Test,5,2),Right(Test,2)))) as [Test Temp];

LOAD * Inline

[

Test

0

20160215

20160317

20160318

20160319

20160322

20160323

20160324

20160326

20160327

20160329

20160330

20160331

20160402

20160403

20160404

20160405

20160406

20160407

20160408

20160409

20160410

20160411

20160412

20160413

20160414

20160415

20160416

20160417

20160418

20160419

20160420

20160425

20160426

20160429

20160502

20170202

20170217

20300101

20300303

20300707

20300909

20500505

30300101

30300330

30300707

30300909

50500505

60160102

60160105

60160106

60160113

60160212

60160215

60160217

60160409

];

4 Replies
sunny_talwar
Not applicable

Re: 0 value in date

I don't seem to understand the issue. 0 is showing up on all the three fields here, isn't it?

Capture.PNG

markgraham123
Not applicable

Re: 0 value in date

It is missing the format in the 'Test Final'.

When format shows up, 0 will not.

In 'Test Final' i'm looking for o and also dates with 'MM/DD/YYYY' format

sunny_talwar
Not applicable

Re: 0 value in date

Try this:

Table1:

LOAD *,

  If(IsNull([Test Temp]),0,[Test Temp]) as [Test Final];

LOAD *,

  If([Test]='0', 0, Date(MakeDate(Left(Test,4),Mid(Test,5,2),Right(Test,2)))) as [Test Temp];

LOAD * Inline

[

Test

0

20160215

20160317

20160318

20160319

20160322

20160323

20160324

20160326

20160327

20160329

20160330

20160331

20160402

20160403

20160404

20160405

20160406

20160407

20160408

20160409

20160410

20160411

20160412

20160413

20160414

20160415

20160416

20160417

20160418

20160419

20160420

20160425

20160426

20160429

20160502

20170202

20170217

20300101

20300303

20300707

20300909

20500505

30300101

30300330

30300707

30300909

50500505

60160102

60160105

60160106

60160113

60160212

60160215

60160217

60160409

];

markgraham123
Not applicable

Re: 0 value in date

oopss!!

I forgot the keyword..

Thanks again man!

Really..ur nailing.