topic Re: Re: Filling blank fields with dates in QlikView

Filling blank fields with dates

Fri, 31 Jan 2014 13:14:54 GMT

People, I have a problem in my database that I have a field called "[Claim Closed Date]", that sometimes it comes blank for me. I would like to create a rule for that, this rule has to be like this: I have another field called "[Status Claim]" and another one called "[Submitted Date]", so, I would like to put like this in the script:

...

[Claim Closed Date],

[Status Claim],

[Submitted Date],

....

if([Claim Closed Date] = "Blank (?)" and [Status Claim] = 'Closed', FILL WITH [Submitted Date] + "30 days",,)

how do I do such thing in the script??

Re: Filling blank fields with dates

jvishnuram — Fri, 31 Jan 2014 13:26:27 GMT

Hi Julio,

Try this

if(isnull([Claim Closed Date]) and [Status Claim] = 'Closed', Date([Submitted Date] + 30)) as EXPECTED

Re: Filling blank fields with dates

Anonymous — Fri, 31 Jan 2014 13:27:35 GMT

if( isnull([Claim Closed Date]) and [Status Claim] = 'Closed', date( [Submitted Date] + 30 ) as [Claim Closed Date] ,

Re: Filling blank fields with dates

maxgro — Fri, 31 Jan 2014 13:31:37 GMT

if( len(trim( [Claim Closed Date] ))=0 and [Status Claim] = 'Closed', date( [Submitted Date] + 30 ) as [Claim Closed Date] ,

Re: Filling blank fields with dates

Fri, 31 Jan 2014 13:34:52 GMT

this one doesn't work fine for me, because i would to be filled out inside the [Claim Closed Date], but thanks

Re: Filling blank fields with dates

Anonymous — Fri, 31 Jan 2014 13:38:00 GMT

Julio

This will put the data into a field called [Claim Closed Date]

if( isnull([Claim Closed Date]) and [Status Claim] = 'Closed', date( [Submitted Date] + 30 ) as [Claim Closed Date] ,

Re: Filling blank fields with dates

Fri, 31 Jan 2014 13:43:42 GMT

It doesn't allow me to have two fields with the same name in this case...

Re: Filling blank fields with dates

Anonymous — Fri, 31 Jan 2014 13:44:47 GMT

Julio

Then get rid of the original one and just leave the new one.

Re: Filling blank fields with dates

Fri, 31 Jan 2014 14:19:13 GMT

i did, it worked, but brings for me no data on this field now...

Re: Filling blank fields with dates

Anonymous — Fri, 31 Jan 2014 14:27:05 GMT

Julio

Can you paste in your load script.

Re: Filling blank fields with dates

Anonymous — Fri, 31 Jan 2014 14:29:29 GMT

Julio

Also could you try it with the suggestion of maxgro

if( len(trim( [Claim Closed Date] ))=0 and [Status Claim] = 'Closed', date( [Submitted Date] + 30 ) as [Claim Closed Date] ,

Mine is searching for Null's, but Massimo's is searching for where the string has length of zero having trimmed of leading & trailing spaces. - which may well be what you need.

Re: Filling blank fields with dates

Fri, 31 Jan 2014 14:45:46 GMT

Thank you Bill, alot!!!!

Re: Filling blank fields with dates

Fri, 31 Jan 2014 16:56:24 GMT

Guys, sorry, my mistake, it did not worked correctly. It brought only the ones that were in blank before, not everything... my script is:

eParts:

LOAD [Claim #],

[Work Order #],

[Aircraft Model],

[Serial Number],

Operator,

[Shop Name] as [Shop Name Claim],

[Shop Code],

[Claim Opened Date],

//([Claim Close Date]),

[Arrival Date],

[Service Complete Date],

[A/C Flight Hours],

[A/C Cycle],

Currency,

[Claim Total],

[Total Approved],

[Total Denied],

[Status Claim],

[Total Scheduled Labor],

[Total Unscheduled Labor],

[Total Service Bulletin Labor],

[Total Other Labor],

[Expendable Parts],

[Total Handling Fee],

[Total Freight],

[Total Miscellaneous],

PAYER,

[Notification #],

[Service Order #],

[Purchase Requisition #],

[PO #],

[PO Date],

//Comments as Comments_eParts,

[PO Total],

[Squawk Item],

[Ref. Item],

Type,

Status,

Date,

[Squawk Total],

[Failure Date],

[Type (Inspection)],

[Source / Discrepancy],

[Description / Corrective Action],

[Man Hours],

[Labor Rate],

[Labor History],

// [Approved? Y / N],

// Comment as Comment_eParts,

Classification,

PO#,

[PN Installed],

[PN Installed Description],

[S/N Installed],

Qty,

[Unit of Measure],

[Your Price],

[List Price],

[Handling Fee],

[Total Handlling],

[CAP / Freight],

[PN Removed],

[SN Removed],

// [Approved? Y / N],

// Comment as Comment1_eParts,

Freight,

[Freigh Total],

// [Approved? Y / N],

// Comments as Comments1_eParts,

Description,

//[Total Miscellaneous],

// [Approved? Y/N],

// Comment as Comment2_eParts,

[Total Amount],

[Sales Org.],

if( len(trim([Claim Close Date]))=0 and [Status Claim] = 'Closed', date( [Claim Opened Date] + 60,[Claim Close Date])) as [Claim Close Date],

IF([Labor Rate]>200,[Labor Rate],) as Wrong_Labor_Rates,

Month([Claim Close Date]) as MONTH,

Year([Claim Close Date]) as YEAR,

Day([Claim Close Date]) as DAY,

FileName() as DATA_SOURCE,

IF([Claim #]=PREVIOUS([Claim #]),'0','1') as UNIQUE_FLAG

FROM

C:\Users\jcloure\Desktop\Feedbackserver_Qlikview\CLAIM_2013_27012014092318.csv

(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

Re: Re: Filling blank fields with dates

Anonymous — Fri, 31 Jan 2014 17:01:24 GMT

Julio

I think you had a misplace bracket, try this.

if( len(trim([Claim Close Date]))=0 and [Status Claim] = 'Closed', date( [Claim Opened Date] + 60 ) ,[Claim Close Date]) as [Claim Close Date],

Red ) added & second ) before as removed so leaving just one )

Best Regards, Bill

Re: Filling blank fields with dates

Mon, 03 Feb 2014 10:12:30 GMT

Thanks Bill, now worked perfectly. I got another problem here with that, but that is in my process, just what I wanted to see exactly

Thank you ALOT!