topic Re: Question on loading external data in QlikView

Question on loading external data

elstanford0430 — Sat, 16 Nov 2024 20:02:58 GMT

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

Re: Question on loading external data

marcus_sommer — Thu, 19 Sep 2019 15:15:52 GMT

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

Re: Question on loading external data

elstanford0430 — Thu, 19 Sep 2019 15:19:44 GMT

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

Re: Question on loading external data

marcus_sommer — Thu, 19 Sep 2019 15:21:52 GMT

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

- Marcus

Re: Question on loading external data

elstanford0430 — Thu, 19 Sep 2019 15:50:33 GMT

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.

Re: Question on loading external data

marcus_sommer — Fri, 20 Sep 2019 06:58:00 GMT

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

Re: Question on loading external data

elstanford0430 — Fri, 20 Sep 2019 11:51:48 GMT

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

Re: Question on loading external data

marcus_sommer — Fri, 20 Sep 2019 12:04:46 GMT

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

Re: Question on loading external data

elstanford0430 — Fri, 20 Sep 2019 12:08:17 GMT

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.

Re: Question on loading external data

marcus_sommer — Fri, 20 Sep 2019 12:29:08 GMT

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

Re: Question on loading external data

elstanford0430 — Fri, 27 Sep 2019 15:12:31 GMT

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.