Question on loading external data

[elstanford0430]

I hope this is the correct Location.

I am loading external data into my Qlikview Analytics dashboard.  I am loading physician specialties and grouping them into service lines in my external file.  There are some blank specialties in my source database that I want to assign to  a group named "Other" along with some other specialties.  The blank will not group since there is no actual value in the specialty.  I tried assigning an XXXX specialty to the practitioners in this group but that will continue to change.  Is there a way to assign a NULL value specialty to a group in my excel load file used for this grouping or some setting in Qlikview that will allow me to group a NULL value?

This external data is used in a pivot table and I don't want it to appear separately in the Service line groupings.  I need it to roll into "Other".

Example of:

Attending Practitioner Specialty	Attending Service Line Name P&L	Attending Specialty
PAXX PATHOLOGY	Other	PAXX
PAAP PATHOLOGY/ ANATOMIC PATHOLOGY	Other	PAAP
TRANS TRANSITIONAL	Other	TRANS
XXXX UNDECLARED	Other	XXXX
	Other

[marcus_sommer]

NULL's arent stored in any way and therefore you couldn't group or access them. If you want this you need to assign a real value to them, like:

if(isnull(YourField), 'NULL / blank / empty / #NV', YourField) as YourField
   or
if(len(trim(YourField)), 'NULL / blank / empty / #NV', YourField) as YourField

- Marcus

[elstanford0430]

Thank you.  I am new at this.  Where do I need to create this formula?  In the load script of somewhere else post load?

[marcus_sommer]

Yes, in the load-script directly within your load-statement.

- Marcus

[elstanford0430]

I attached my script.  I am not sure why "Your field" is listed twice in your directions.  I would really appreciate it if you could take a look and let me know how to write this new statement.

Thanks so much in advance. 

[marcus_sommer]

It was listed twice to show that there are in general multiple ways to query NULL whereby real NULL's exists only the databases or are the result of certain functions. The most users don't really differ between NULL or empty or spaces or similar values but by querying them it makes differences and therefore my example with: isnull() and alternatively the usually more suitable way of querying the lenght with: len(trim()).

In your case I'm not quite sure what do you really want to check and what should be the result. My suggestion was a single if-loop of: if(condition = true, then, else [optional]) and you may with it querying the value(s) of one/several fields and returning one or the other fieldvalue and/or alternativ strings for the then/else-branch and/or you could nest further if-loops to create a cascade of conditions. Nothing of them is really difficult - you need only from the syntactically point of view to ensure that the above mentioned order of if-then-else with the closing brackets are set.

And it may look like:

if(len(trim([Attending Practitioner Specialty])), '#NV', [Attending Service Line Name P&L]) as YourField
   or
if(len(trim([Attending Practitioner Specialty])), '#NV',
   if([Attending Service Line Name P&L]='Other', [Attending Service Line Name P&L], 'any other value')) as YourField

- Marcus

[elstanford0430]

Thanks Marcus.  Based on your description, did I add the information to the script I attached correctly?

[marcus_sommer]

No, it's not correct - therefore my trial to explain logic and syntax and providing some examples which may give you some ideas. Just play a bit with them and adjust them here and there and check the results.

- Marcus

[elstanford0430]

I do not think that I know enough to do that which is why I requested assistance.  I have only done basic formulas in Analytics so I will try but without the knowledge I am not sure I know what to do.

[marcus_sommer]

I'm not sure that I'm able to explain it more simply as I did. I can only recommend that you tries the various suggestions - in general you will always need n trials to ensure that your logic fetched all possibilities, is stable and performant and that you understand their logic respectively the differences between various approaches. Nobody could save you for this work.

And quite often this is not difficult and makes even a lot of fun - most helpful for it is often not to do it with the real data else just to create some dummy-data and doing everything step by step - if the single steps work you merge them together and then going foreward with the next steps ... and after that you could transfer it to your real world.

- Marcus