Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
elstanford0430
Partner - Contributor III
Partner - Contributor III

Question on loading external data

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 SpecialtyAttending Service Line Name P&LAttending Specialty
PAXX PATHOLOGYOtherPAXX
PAAP PATHOLOGY/ ANATOMIC PATHOLOGYOtherPAAP
TRANS TRANSITIONALOtherTRANS
XXXX UNDECLAREDOtherXXXX
 Other 
Labels (3)
1 Solution

Accepted Solutions
elstanford0430
Partner - Contributor III
Partner - Contributor III
Author

I was able to get some more detailed assistance on this issue.  The practitioners' specialty was used to assign the service line.  The blanks occurred because there were no practitioners on the records at all, not because the specialty was blank.  A long script was written for me to populate a specialty in Qlikview where no practitioner exists with "No Pract" and then I was able to assign this description in the external excel table and it worked.

View solution in original post

10 Replies
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
Partner - Contributor III
Partner - Contributor III
Author

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
Partner - Contributor III
Partner - Contributor III
Author

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
Partner - Contributor III
Partner - Contributor III
Author

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
Partner - Contributor III
Partner - Contributor III
Author

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