Qlik Community

Ask a Question

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Partner
Partner

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
Partner
Partner

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
MVP & Luminary
MVP & Luminary

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

Partner
Partner

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

MVP & Luminary
MVP & Luminary

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

- Marcus

Partner
Partner

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. 

MVP & Luminary
MVP & Luminary

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

Partner
Partner

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

MVP & Luminary
MVP & Luminary

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

Partner
Partner

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.

MVP & Luminary
MVP & Luminary

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