topic Re: Data Manipulation inside Load Script in QlikView

Data Manipulation inside Load Script

Tue, 23 Oct 2012 12:19:31 GMT

Hi to all,

I have several issues to resolve. I need to:

1 convert string in date format in the load script

2 create two calculate field that are the difference in network days

For the first isse a tried

Table1:

SQL SELECT DateField,

Position

Number

FROM Table_from_the_system;

Load Position,

Number,

date(date#(DateField,'DD/MM/YYYY hh:mm:ss'),'DD/MM/YYYY')as NewDate RESIDENT Table_from_the_system;

But doesn't work !! The NewDate field is Empty

( Datefield is a string )

Re: Data Manipulation inside Load Script

Gysbert_Wassenaar — Tue, 23 Oct 2012 12:31:09 GMT

networkdays(Date1,Date2) returns the net number of workdays between Date1 and Dat2 (as long as they are dates and not strings).

Check that the date format matches the format of the DateField string.

Also you use resident Table_from_the_system. But looking at your example don't you need resident Table1 instead?

Re: Data Manipulation inside Load Script

Tue, 23 Oct 2012 12:41:54 GMT

Thanks Gysbert,

the networkdays works.

But the main problem is the string "date(date#(DateField,'DD/MM/YYYY hh:mm:ss'),'DD/MM/YYYY')as NewDate"

that doesn't work and I don't know why.

I wrong to copy "Resident Table_from_the_system is wrong" the correct string is "Resident Table1"

Re: Data Manipulation inside Load Script

Anonymous — Tue, 23 Oct 2012 12:43:32 GMT

I'd expect that the DateField in the database has date format already, so the syntax is:

date(DateField) as NewDate

date(floor(DateField)) as NewDate // to remove time part

date(floor(DateField), 'DD/MM/YYYY') as NewDate // to enforce format

If it is not a date field, you need to use date#() as well - make sure you got the format right in this case.

Regards,

Michael

Re: Data Manipulation inside Load Script

MayilVahanan — Tue, 23 Oct 2012 12:45:39 GMT

Did you try

date(DateField,'DD/MM/YYYY') as NewField

date(timestamp(DateField,'DD/MM/YYYY hh:mm:ss'),'DD/MM/YYYY')

Am not sure why you use date#(), are you sure , its text format??

Re: Data Manipulation inside Load Script

Tue, 23 Oct 2012 13:12:12 GMT

It Works !!

with the second solution, but isn't clear for me why works.

What do the timestamp function ?

Thanks

Re: Data Manipulation inside Load Script

MayilVahanan — Tue, 23 Oct 2012 13:20:45 GMT

From help:

The timestamp function formats the expression as a date and time according to the string given as format-code. If the format code is omitted, the date and time formats set in the operating system are used.

And i think, from sql format is like DD/MM/YYYY hh:mm:ss format..so timestamp calculate the value exactly n give the result..