topic Re: Upload fields depending on whether they have value in QlikView

Upload fields depending on whether they have value

Stravan — Sat, 16 Nov 2024 04:30:17 GMT

Hi everybody, need help with the following question:I have my script below where I want to upload fields Injuryposition1-6 depending on whether they have value. For example, if field 6 does not have data, I do not want to upload it, how should I do it in the script?

I have many tables that contain all the fields whether they have value or not, I do not want to show those who do not have value. I do not want to change table box to chart or stright table. If there is some way to be able to display fields in a table box if they have value would also solve my problem.

PERSON:
Load Distinct
Num(Accident_nr) as Person_OlycksID,
XSweref as Person_X_sweref,
YSweref as Person_Y_sweref,
Injuryposition1 as Person_Injuryposition1,
InjuryPosition2 as Person_Injuryposition2,
InjuryPosition3 as Person_Injuryposition3,
InjuryPosition4 as Person_Injuryposition4,
InjuryPosition5 as Person_Injuryposition5,
InjuryPosition6 as Person_Injuryposition6
FROM UOS.qvd (qvd);

Re: Upload fields depending on whether they have value

vishsaggi — Sat, 09 Feb 2019 04:43:20 GMT

In Table box its not possible you might have to write multiple script lines to handle this or use straight table with conditional show hide of columns in your dimension tab. Have to think how to do this in the script;

Re: Upload fields depending on whether they have value

pradosh_thakur — Sat, 09 Feb 2019 04:55:15 GMT

Can you please come up with a few lines a sample data and the output you are looking for. It will be easy helping you out. I can understand what you are asking but the dummy/sample data will make it easier.

Re: Upload fields depending on whether they have value

Stravan — Sat, 09 Feb 2019 20:38:10 GMT

Hi Pradosh,

Here is the data. As you can see, "Injuryposition6" does not have data.

Thank you for all help.

LOAD * Inline [
OlycksID, X-sweref, Y-sweref, Injuryposition1 , Injuryposition2, Injuryposition3, Injuryposition4, Injuryposition5, Injuryposition6
17810, 6609700, 588230, , , , , ,
17810, 6609700, 588230, 1, , , , ,
17899, 6281281, 363189, 1, 1, , , ,
17899, 6281281, 363189, 2, , , , ,
17552, 6407389, 321385, 1, , , , ,
17533, 6374128, 323714, 2, 2, 1, 1, ,
17533, 6374128, 323714, 1, , , , ,
17533, 6374128, 323714, 3, 2, 2, 2, 2,
17522, 6199067, 437634, 1, 1, , , ,
17522, 6199067, 437640, 1, , , , ,
17508, 6164066, 375652, 1, 1, 1, 1, 1,
17501, 6164458, 376946, 1, 1, , , ,
17408, 6598310, 412482, , , , , ,
17330, 7087864, 788882, 2, 2, 1, 1, ,
16858, 6161588, 375130, 1, , , , ,
17430, 6153185, 388419, 1, , , , ,
16774, 6161337, 374106, 1, 1, 1, , ,
16655, 6287230, 370764, 1, 1, 1, , ,
16655, 6287230, 370764, 1, , , , ,
];

Re: Upload fields depending on whether they have value

marcus_sommer — Mon, 11 Feb 2019 12:48:00 GMT

It's not quite clear for me what do you want to do - should the field really be removed or be hidden. In both cases you could use fieldvaluecount('YourField') to check if fieldvalues exists and then drop the field or set the visibility to FALSE (by dependencies to other dimension-values could also a count() be useful).

Another could be to change the data-structure with The-Crosstable-Load.

- Marcus

Re: Upload fields depending on whether they have value

Stravan — Thu, 14 Feb 2019 08:48:44 GMT

Hi Marcus, it doesn't matter if it gets hidden or removed.

I have a table (Box table, do not want to use chart or stright table) with the 9 variables as default and as soon as one of them does not have value I do not want to show it in the table nor come up with when I export data from the table to Excel.

Don't know if it's better to do it in the table or code it directly in the script, and if so, how?

Would be grateful if you can use the data above to create a qvw example. I'm quite a beginner with qlikview 🙂

Thanks in advance

Re: Upload fields depending on whether they have value

marcus_sommer — Thu, 14 Feb 2019 08:57:00 GMT

Why not using a straight-table and applying the appropriate conditions to the visibility of the columns?

- Marcus

Re: Upload fields depending on whether they have value

Stravan — Thu, 14 Feb 2019 09:20:02 GMT

Hi agin Marcus, It is so that I have over 100 variables in a table and tested with stright table but the problem was that you could not change the width of the table (no scroll) so it was not manageable.

Can I in the script remove variables that do not have value when downloading data? So, how?

Thank you for all help!

Re: Upload fields depending on whether they have value

marcus_sommer — Thu, 14 Feb 2019 09:30:31 GMT

If you put your fields within expressions and not in dimensions you will get scrollbars again.

Beside this I strongly recommend to rethink your whole approach. It's far away from best practice to create such wide tables even if they are mainly used for exporting the data into other tools (it's only for exporting it wouldn't be meaningless if there are scrolls or not) and it means also that you handles a big crosstable within the datamodel which is really seldom necessary. Just take a closer look to the above provided link.

- Marcus

Re: Upload fields depending on whether they have value

Stravan — Thu, 14 Feb 2019 10:28:21 GMT

Thanks Marcus for the recommendation, I'll actually look at it.

But I still want to know if there is any formula in the script where one can delete a field if it does not have data when loading data?I think of what you wrote earlier above about fieldvaluecount (), how did you think? Can you do that in the script?

Re: Upload fields depending on whether they have value

marcus_sommer — Thu, 14 Feb 2019 11:26:44 GMT

You will need some kind of routine for it, for example something like this:

for i = 1 to nooffields('table')
   let vField = fieldname($(i), 'table');
   if wildmatch('$(vField)', '*my fields*') then
      if fieldvaluecount('$(vField)' then
         trace $(vField) has values;
      else
         trace $(vField) has been removed;
         drop fields [$(vField)];
      end if
   end if
next

- Marcus

Re: Upload fields depending on whether they have value

Stravan — Thu, 14 Feb 2019 14:23:11 GMT

Hi again Marcus and thank u very much for your help!

An qusetion that does not directly relate to this post. Do you know what is the corresponding function in QlikView for Excel Degrees ()?

Re: Upload fields depending on whether they have value

marcus_sommer — Thu, 14 Feb 2019 14:45:20 GMT

I don't know if such function exists but if I look on some google-results it seemed to be quite simple and could be easily calculated or maybe replaced with variable-function like:

eDegrees:
round(180 / pi() * $1, 0.0001)

and then you could call it with:

$(eDegrees(YourFieldOrNumber))

for example with:

$(eDegrees(0.785398163)) = 45

You might choose a different rounding and/or add any kind of formatting to it.

- Marcus

Re: Upload fields depending on whether they have value

Stravan — Thu, 14 Feb 2019 14:58:44 GMT

Thanks again!

It is so, I need to convert/transform loads of SWEREF99TM position coordinates to WGS84 in order to display them in Google map in QlikView.

I have an excel formula for this conversion where there there is the Degree () function. But you might know a finished code for this transformation?

Re: Upload fields depending on whether they have value

marcus_sommer — Thu, 14 Feb 2019 15:49:55 GMT

Did you try my example? It should work like the excel-function.

- Marcus

Re: Upload fields depending on whether they have value

Stravan — Thu, 14 Feb 2019 17:24:05 GMT

It is not working, it is sure I am wrong.

I have my field eg 'Xsweref' in a table but you mean that I have to create my own table (eDegrees)?

Re: Upload fields depending on whether they have value

marcus_sommer — Fri, 15 Feb 2019 07:47:21 GMT

If the variable is created like above mentioned with:

round(180 / pi() * $1, 0.0001)

in the UI respectively in the script with:

set eDegrees = "round(180 / pi() * $1, 0.0001)";

you could call it in nearly the same fashion as a normal function:

$(eDegrees(YourFieldOrNumber))

and this is quite independent to where you execute your transformation respectively this depend only on your business logic.

- Marcus

Re: Upload fields depending on whether they have value

Stravan — Sat, 16 Feb 2019 17:26:26 GMT

Thank you Marcus for the latest help with degrees ().

The code for not displaying the fields that do not have value works but it has a problem, it takes every other entry of those who do not have values such as Injuryposition_6, Injuryposition_8, etc. Do you know why?

Re: Upload fields depending on whether they have value

Stravan — Mon, 18 Feb 2019 11:45:57 GMT

Thank you Marcus for the latest help with degrees ().

The code for not displaying the fields that do not have value works but it has a problem, it takes every other entry of those who do not have values such as Injuryposition_5, Injuryposition_7, etc. Do you know why?

Re: Upload fields depending on whether they have value

Stravan — Tue, 19 Feb 2019 08:47:34 GMT

Dear Marcus, did you have any explanation why your code takes every other field not all?