topic Cross Table, Value 'x' should be true/false in QlikView

Cross Table, Value 'x' should be true/false

Mon, 18 Aug 2014 16:08:32 GMT

Hello Qlikview-jedi's,

With some tips of Marcus Sommer I have created a cross table. However I run into a new problem and instead of contineuing on the same thread it would be wiser to start a new discussion.

The values it returns are 'x' which.Fair enough that is exactly as in my excel data dump (see picture). However I want it TRUE or FALSE. Like this I can make a dimension/further selection. How do I indicate this to qlikview, or change my excel file?

Example attached. ID 3342 Should only have as selection Incubator 14. Instead it shows all.

Marcus mentioned:

This isn't possible within the crosstable-load but in a further load you could simply ask if(YourField = 'x', true(), false()) or you used a mapping-table and applymap().

I have no idea how I do this further load.

tb1:

CrossTable(Incubator, Data)

LOAD ID,

Incub.1,

Incub.2,

Incub.3,

(etc. etc.)

Cheers for your help guys.,

Jarl

Re: Cross Table, Value 'x' should be true/false

JonnyPoole — Mon, 18 Aug 2014 16:16:22 GMT

You can do a subsequent resident load that alters the 'X's' to Trues and Falses.

This examples includes all the fields you loaded and then adds a new expression that uses True and False rather than X's.

tb2:

Load

if( <Yourfield>='X','True','False') as NewValue

Resident tb1;

drop table tb2;

Re: Cross Table, Value 'x' should be true/false

Mon, 18 Aug 2014 17:38:01 GMT

Thanks Jonathan.

I am a step closer. I have adapted your formula to work in my context:

Load

if( Data='x','True','False') as TFIncubators

Resident tb1;

(note i didnt drop out the table just yet).

Attached I have the pictures of the two variables (Incubators) and than TFIncubators. Even though I got a special selection (Product A, in Year 2013, etc.) All Incubators are white (available). Only when I select TRUE on TFincubators does it get greyed out (those that are FALSE)

However I just want this to be inherent. I should not make the selection true or false. Always show true.

I want this to be the case for my Incubator variable, without having to select "True".

Re: Cross Table, Value 'x' should be true/false

JonnyPoole — Mon, 18 Aug 2014 17:45:41 GMT

Maybe you only want to load the 'Xs' / Trues:

Load

if( Data='x','True','False') as TFIncubators

Resident tb1

Where Data='X';

Re: Cross Table, Value 'x' should be true/false

Mon, 18 Aug 2014 18:19:43 GMT

Getting closer (see picture). So it drops False from the TFIncubator field.

However I want it to be filtered in the Incubator table. So I added the same wording at the end of the cross-table but that didnt work.

(i am not sure if I am making sense. I am just trying to make the True and False inherent to the Incubator field)

Re: Cross Table, Value 'x' should be true/false

JonnyPoole — Mon, 18 Aug 2014 18:25:30 GMT

Did you ever drop the original table ? Otherwise It would continue to show incubators where there is no X ...

Or if you want both add a new incubator field like this and use 'newincubator' as your list box.

Load

if( Data='x','True','False') as TFIncubators,

if( Data ='x', Incubator) as NewIncubator,

Resident tb1

Where Data='x';

Re: Cross Table, Value 'x' should be true/false

simenkg — Mon, 18 Aug 2014 18:25:34 GMT

Why not simply lock the selection on True? Right click the list box and select Lock. If you want the selection to be "behind the scenes" put the line
Set HidePrefix = '%'; in the start of your script and change the line to

if( Data='x','True','False') as %TFIncubators

If you just want to have True selected by default but be able to clear it then add a trigger on open:

Document settings - Triggers - On Open - Add Actions - Add - Selection - Select in field- TFIncubators.

The document will now automatically select True when opened.

Re: Cross Table, Value 'x' should be true/false

Mon, 18 Aug 2014 18:35:45 GMT

Perfect! Thanks Jonathan. Simen your answer is also correct and actually used it for another issue so that saved me putting up another post.

Take care guys.