topic String Function in Set Analysis in QlikView

String Function in Set Analysis

Anonymous — Mon, 22 Nov 2010 17:35:32 GMT

My NT ID comes in the form of NTLOCAL\john.smith

I need to change time based on the logged in user. Each user's GMT Offset is stored in the database. When I hard code the value, I get the correct value:

=sum({<[DB User Name] = {'John Smith'}>} [GMT Hour Offset])

The below string also gives the correct value:

=trim(capitalize(replace(mid(OSUser(),9),'.',' '))) gives a value : John Smith.

However, the below set analysis while correct in syntax, does not give the correct value:

=sum({<[DB User Name] = {$(=trim(capitalize(replace(mid(OSUser(),12),'.',' '))) )}>} [GMT Hour Offset])

Where are I going wrong? Any better suggestions?

Thanks,

Dinesh.

String Function in Set Analysis

Miguel_Angel_Baeyens — Mon, 22 Nov 2010 18:22:03 GMT

Hi Dinesh,

Check that the result of the function is quoted as a literal:

=sum({<[DB User Name] = {'$(=trim(capitalize(replace(mid(OSUser(),12),'.',' '))) )'}>} [GMT Hour Offset])

EDIT: Ok, there may be some quotes missing. What about creating a new table (say inline) where you store in a field the offset value for each user? Then, your expression will look like

=sum([Time Field] + [GMT Hour Offset])

I'd give it a shot, since once the user has logged, the value is not going to change.

Hope that helps

String Function in Set Analysis

Anonymous — Mon, 22 Nov 2010 18:26:23 GMT

That was it. For some reason, the editor shows a syntax error when the quotes are included, but it gives the correct results.

Dinesh.

String Function in Set Analysis

Anonymous — Mon, 22 Nov 2010 19:05:45 GMT

When I use your formula =sum([Time Field] + [GMT Hour Offset]), I get 40499.416666667. How do I format it to the right date time format?

Thanks,

Dinesh.

String Function in Set Analysis

Miguel_Angel_Baeyens — Mon, 22 Nov 2010 20:00:14 GMT

Hi Dinesh,

Use the following:

=Date('40499,416666667', 'DD/MM/YYYY hh:mm:ss')

It will return "17/11/2010 10:00:00"

Hope that helps