topic Re: Cross table / dummy variable in QlikView

Cross table / dummy variable

Wed, 13 Aug 2014 17:28:42 GMT

Hello Community, apologies if the title is incorrect- i wasn't sure how to name it.

Attached you find a snip of the excel sheet. There is a few (55) columns with the title Incubator ##. The cells are populated with an 'x'.

Initialled it wasn't an issue loading the file in qlikview. It just created a lot of dimensions but I could still make my graphs using :

avg({<[Incub. 1]={'x'}>}%F). With year as dimension. It used a lot of time to create each individual expression, but did the trick.

However now that I want Incub. ## as dimension I am running into problems

I looked into cross tables but this does not seem to do the trick for me. I also don't think this is the way to go. I then tried a

LOAD*INLINE

[ Incubator

Incub.1

..];

But then it is just merely text and not linked to the actual column Incub. 1 in the excel document.

Any help is welcome, thanks.

Re: Cross table / dummy variable

marcus_sommer — Wed, 13 Aug 2014 19:09:16 GMT

Crosstable is the right approach - I think you will need a little more play around with the logic and the syntax to create a table-structure like these:

Any_ID, SomeDate, Incub, Value

1, 2014/08/13, 1, x

1, 2014/08/13, 2, x

.....

Look on these or similar postings to receive some useful hints:

Re: loading 2 level cross tables

Re: Extracting parts of field names to create new fields

Re: Check CrossTable for NULL Values

- Marcus

Re: Cross table / dummy variable

MarcoWedel — Wed, 13 Aug 2014 21:18:21 GMT

can you post some sample data?

thanks

regards

Marco

Re: Cross table / dummy variable

Thu, 14 Aug 2014 12:29:19 GMT

Thanks Marcus for the links. Thanks Marco for taking your time and willing to help out. I have managed to solve the problem by adding an additional column to my original data as a unique ID key which wasn't before. This solved my problem with the crosstables.

Re: Cross table / dummy variable

Thu, 14 Aug 2014 13:43:51 GMT

Sorry, I do have to addin another question. If I need to make a new post let me know.

I have made the crosstable, and the values it returns are 'x'. Fair enough that is exactly as in my excel data dump. 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.

Re: Cross table / dummy variable

marcus_sommer — Thu, 14 Aug 2014 13:51:47 GMT

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().

- Marcus

Re: Cross table / dummy variable

Thu, 14 Aug 2014 14:00:16 GMT

Currently I got:

tb1:

CrossTable(Incubator, Data)

LOAD ID,

Incub.1,

Incub.2,

Incub.3,

(etc. etc.)

I beleive with further load you refer to the dropping tables afterwards like in link 1. However I do not understand how to apply the condition if(...) in creating a table. Could you support me with an example link / code so I can adapt it.

Thanks a bunch Marcus