Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Evey body,
I have a data Model that two table of this Model connected to each Other with the key. But there is a Problem, in one Table in the special day for special ID we have data but for an other the data is 0. In our Table we have measure that effected with the 0 value and we do not want, is there any solution?
just for info:I have try to ignore column effect in measure like Columnname=
but it still take effect.
You want to omit rows where data is equal to 0? So you can either do that in the load from the table load script with a where clause or if you need in the model and just want to remove from the object you could do with set analysis. If I have picked you up correctly something like this should work:
=count({< data -= {0} >} data)
Hi every one,
Actually this is not the problem. I try to explain in an other way.
Just imagine that you have some Tables, two of this tables via one %key Concatenate to each other but the problem is that in one table we have some ids that in an other we do not have, that is why we have 0 in one measure.
Is there any idea what should we do, when in Qlik Sense by Concatenate one Table have some ids that an other one does not have?
Thanks in Advanced
Sama
Can you share your script? Concatenating the two tables will bring in the additional rows regardless of if an ID is missing. Like I am just not clear what the issue is. For example if I do this:
Table1:
LOAD ID1&'-'&ID2 as %Key,
Measure Inline [
ID1, ID2, Measure
1, 10, 100
2, , 50
3, 8, 20
4, , 100
5, 6, 50
6, 5, 20
7, , 100
8, 3, 50
9, 2, 20
10, 1, 100
11, , 50
12, , 20
13, , 100
];
Concatenate(Table1)
LOAD ID1&'-'&ID2 as %Key,
Measure Inline [
ID1, ID2, Measure
1, 10, 50
2, 9, 20
3, 8, 100
4, , 50
5, 6, 20
6, 5, 100
7, , 50
8, , 20
, 2, 100
10, 1, 50
11, , 20
12, , 100
13, , 50
];
Even if keys don't exist they will still be brought into the model. Is it because some of your measures are nulls and you want them as zeros to aggerate overall? Or are you maybe looking for a Full Outer Join and not a concatenation?