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

[#1]

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

[JonnyPoole]

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;

[#3]

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".

[JonnyPoole]

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

Load

     *,

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

     Resident tb1

Where Data='X';

[#5]

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)

[JonnyPoole · Accepted solution]

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

[simenkg]

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.

[#8]

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.