Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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);
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
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
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.
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, , , , ,
];
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
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
Why not using a straight-table and applying the appropriate conditions to the visibility of the columns?
- Marcus
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!
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
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?