Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Stravan
New Contributor

Upload fields depending on whether they have value

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

Tags (1)
Labels (2)
2 Solutions

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Upload fields depending on whether they have value

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

MVP & Luminary
MVP & Luminary

Re: Upload fields depending on whether they have value

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

22 Replies
vishsaggi
Esteemed Contributor III

Re: Upload fields depending on whether they have value

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;

pradosh_thakur
Honored Contributor II

Re: Upload fields depending on whether they have value

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.

Learning never stops.
Stravan
New Contributor

Re: Upload fields depending on whether they have value

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, , , , ,
]
;

Highlighted
MVP & Luminary
MVP & Luminary

Re: Upload fields depending on whether they have value

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

Stravan
New Contributor

Re: Upload fields depending on whether they have value

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 Smiley Happy

Thanks in advance

MVP & Luminary
MVP & Luminary

Re: Upload fields depending on whether they have value

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

- Marcus

Stravan
New Contributor

Re: Upload fields depending on whether they have value

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!

MVP & Luminary
MVP & Luminary

Re: Upload fields depending on whether they have value

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

Stravan
New Contributor

Re: Upload fields depending on whether they have value

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?