topic Re: Fill In The Blank in QlikView

Fill In The Blank

pandreozzi — Wed, 08 Nov 2017 19:45:58 GMT

I am trying to create an expression within a dimension that populates a cell in a chart.

The expression should look at the "Resolution Status" and the "Record Class"

The "Record Type" field should have values there where values are not on the spreadsheet that is used as a source and need to be populated with either the name "Asset" or "Site"

I have tried different expressions that seem to be ok in syntax but do not fill in the empty fields in the Record Type column.

What I am trying to do is this:

If([Resoluttion Status] = 'NEW" and [Record Class]='Router,1,0 [Record Type]='Asset')

This maybe wrong above but I a trying to fill in the - with Asset or Site

Any help is appreciated.

Thanks !!!

Re: Fill In The Blank

jrepucci15 — Wed, 08 Nov 2017 19:50:35 GMT

Try something like:

= IF([Resolution Status] = 'NEW" and [Record Class]='Router',

'Asset',

'Site')

Or use field names in places of the strings I used.

Re: Fill In The Blank

pandreozzi — Wed, 08 Nov 2017 19:55:40 GMT

Thanks John. I will try that and let you know if that worked.

Re: Fill In The Blank

pandreozzi — Thu, 09 Nov 2017 12:44:21 GMT

I tried this in a couple of different places.

Under the Load and within the dimension for the cell and this still will not replace the hyphen - with the word Asset. The syntax seems to be correct as in both cases I do not get errors in the debug. I also tried using the "Case Where"

if([Resolution Status],'NEW' & [Record Class],'Router'),[Record Type]='Asset'

Re: Fill In The Blank

pandreozzi — Thu, 09 Nov 2017 12:52:30 GMT

Here is my expression within the dimension. If I can fix the syntax on this it may work.

=If(IsNull([Record Type]),if(DR01_Inactive_sites_with_installed_inventory=1 or HR05_Invalid_CLLI=1,'Site','Asset'),[Record Type], if([Resolution Status]='NEW' and [Record Class]='Router'),[Record Type],'Asset')

any ideas will be appreciated

Re: Fill In The Blank

PrashantSangle — Thu, 09 Nov 2017 12:54:54 GMT

Hi,

Is it possible to share sample file

Note : To learn qlik function you can use Help menu It will provide sample and syntax.

if ‒ QlikView

Regards,

Prashant

Re: Fill In The Blank

PrashantSangle — Thu, 09 Nov 2017 12:56:10 GMT

can you tell us logic of your if condition???

Regards,

Re: Fill In The Blank

pandreozzi — Thu, 09 Nov 2017 13:21:12 GMT

Sure. I have two data sources, one being an excel spreadsheet and the other being a db. The chart is populated by both. In the event the router is in the spreadsheet the cells in the chart are populated correctly. If the router is not in the excel spreadsheet the Resolution Status cells will show “NEW” and the Record Class cells “Router” when this happens the Record Type will be a hyphen. What I am attempting to do is key off the text “NEW” and the text “Router” and if this is true than replace the hyphen with the text “Asset”

I have tried an "if" statement in the LOAD part of the script and have tried placing logic within the dimension and in both cases most of the time the syntax is correct by the results are not what is expected.

Thanks Prashant

Re: Fill In The Blank

pandreozzi — Thu, 09 Nov 2017 13:23:34 GMT

Here is my if statement within the LOAD

if([Resolution Status]='NEW' & ([Record Class]='Router') as Asset_Router

Re: Fill In The Blank

PrashantSangle — Thu, 09 Nov 2017 13:32:01 GMT

Hi,

As far as what i understood from explanation, below expression might help

if([Resolution Status]='New' and [Record Class]='Router','Asset','Router')

Use it as expression in chart

Regards,

Re: Fill In The Blank

pandreozzi — Thu, 09 Nov 2017 14:29:28 GMT

Prashant, your example above works but changes the text 'Asset' to the text 'Router' where true. What I am trying to do is to replace the hyphen with the word Asset. I am pasting my example.

Currently [Record Type] has a value of -

=if([Resolution Status]='NEW' and [Record Class]='Router','[Record Type],'Asset')

Re: Fill In The Blank

PrashantSangle — Thu, 09 Nov 2017 14:37:42 GMT

There is syntax error always check bracket , single quote

Try below

=if([Resolution Status]='NEW' and [Record Class]='Router',[Record Type],'Asset')

Re: Fill In The Blank

pandreozzi — Thu, 09 Nov 2017 14:46:35 GMT

I did realize that syntax error and corrected it however the results are still the same. I am looking into using the "If Match" in my expression also. I will keep posting results and your assistance is much appreciated.