topic Re: Format number in load script in QlikView

Format number in load script

tmumaw — Fri, 20 Jan 2017 16:45:22 GMT

Hi everyone,

Need a little help in getting my number format right in my load script. Here is what I have.......but not working.

Thanks in advance.

Num((if(Len(trim(Acq_Hire_Date) > 0), Num(LastWorked_Date) - Num(Acq_Hire_Date), Num(LastWorked_Date) - Num(HireDate)) / 365,'00')) as [LOS]

eespiritu — Fri, 20 Jan 2017 16:55:46 GMT

Hi Thom,

Try this:

Num((if(Len(trim(Acq_Hire_Date) > 0), Num(LastWorked_Date) - Num(Acq_Hire_Date),Num(LastWorked_Date) - Num(HireDate)) / 365,'#,##0.00')) as [LOS]

Regards,

Enrique.

sunny_talwar — Fri, 20 Jan 2017 16:58:18 GMT

May be this

Num(

(If(Len(trim(Acq_Hire_Date) > 0,

LastWorked_Date - Acq_Hire_Date,

LastWorked_Date - HireDate)/365),

'00') as [LOS]

Anil_Babu_Samineni — Fri, 20 Jan 2017 16:58:51 GMT

May be try this?

If(Len(Trim(Acq_Hire_Date))>0, Num(LastWorked_Date - Acq_Hire_Date), Num((LastWorked_Date - HireDate)/365)) as LOS

Split to diff expressions and then try

tmumaw — Fri, 20 Jan 2017 17:10:41 GMT

nope

tmumaw — Fri, 20 Jan 2017 17:11:22 GMT

nope

sunny_talwar — Fri, 20 Jan 2017 17:14:21 GMT

Can you share a screenshot?

Anonymous — Fri, 20 Jan 2017 17:14:47 GMT

Num((if(isnull(Acq_Hire_Date),Num(LastWorked_Date) - Num(HireDate), Num(LastWorked_Date) - Num(Acq_Hire_Date)) / 365,'00')) as [LOS]

tmumaw — Fri, 20 Jan 2017 17:46:57 GMT

Got it to work. Thanks everyone for your help.

Num((if(Len(trim(Acq_Hire_Date) > 0), Num(LastWorked_Date) - Num(Acq_Hire_Date), Num(LastWorked_Date) - Num(HireDate)) / 365),'00') as [LOS]

sunny_talwar — Sat, 21 Jan 2017 15:26:05 GMT

Great, if you got what you wanted, I would suggest closing this thread by marking your own response as the correct answer.

Best,

Sunny