Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
markgraham123
Specialist
Specialist

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??

1 Solution

Accepted Solutions
sunny_talwar

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

];

View solution in original post

4 Replies
sunny_talwar

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

Capture.PNG

markgraham123
Specialist
Specialist
Author

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

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
Specialist
Specialist
Author

oopss!!

I forgot the keyword..

Thanks again man!

Really..ur nailing.