Upload fields depending on whether they have value

[Stravan]

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);

[marcus_sommer · Accepted solution]

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

[Stravan]

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 ()?

[marcus_sommer]

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

[Stravan]

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?

[marcus_sommer]

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

- Marcus

[Stravan]

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)?

[marcus_sommer · Accepted solution]

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

[Stravan]

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?

[Stravan]

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?

[Stravan]

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