topic Re: How to use raw headers in excel as drop box 2 in QlikView

How to use raw headers in excel as drop box 2

Anonymous — Mon, 07 Sep 2015 07:56:57 GMT

@Hi I am re- posting my previous question again but with Q file.

I want to create separate drop lists for each OS / COL.

Tried to follow a previous answer but could not really work because I am using several tabs in same source file. As well as Syn linkage appeared.

Please note my remarks.

Marcus_Sommer‌

New to QlikView‌

How to use raw headers in excel as drop box‌

‌

Re: How to use raw headers in excel as drop box 2

avinashelite — Mon, 07 Sep 2015 08:04:01 GMT

Drop or delete the first collection table that should solve the syn error ...since you have loaded the collection table with cross table load you need to rename the first table columns or the new table column otherwise the SYN key will form

Re: How to use raw headers in excel as drop box 2

Anonymous — Mon, 07 Sep 2015 08:27:19 GMT

the Syn disappeared but drop list has no impact on selection

could you please apply it to the attached Q file. Thanks

Re: How to use raw headers in excel as drop box 2

marcus_sommer — Mon, 07 Sep 2015 10:42:08 GMT

Removing the synthetic keys by dropping tables/fields or renaming from the fields is definitely recommended. But this alone makes not a proper datamodel. I think in your case you have no connection between the crosstable and the other tables after (simply) removing the synthetic keys.

I assume you will need some kind of composite key to associate your tables right. Have a special look on the first three links from this posting: Get started with developing qlik datamodels to create a working datamodel.

- Marcus

Re: How to use raw headers in excel as drop box 2

Anonymous — Mon, 07 Sep 2015 12:18:47 GMT

Dear Marcus,

Thanks for the links so useful, but in my case it did not solve the problem.

I am trying to apply what have you given previously

crosstable:

crosstable(DropList, Amounts, 5) Load * From SourceFile;

I find it close to what I need, but if I choose 2 and above, result will show syn ------- (DropList, Amounts, 5). while if I choose 1, syn will disappear but no connection between dropList and data.

Please reflect the solution in the original attachment.

Re: How to use raw headers in excel as drop box 2

marcus_sommer — Mon, 07 Sep 2015 12:49:54 GMT

The number within the crosstable-statement is for the number of fields which will be handled as normal fields before the crosstable-transforming begins. Your challange isn't the crosstable itself you need to build a proper datamodel which meant you need to associate your fields correctly - which one do you need and which one from them need to be combined to a composite key couldn't I say.

I suggest you read the above mentioned postings carefully and builds your datamodel step by step - first one table, then the next table and so on - by this approach you might need to change former successfully associations if you are going further to gain a newer proper association but you will need these additionally efforts then you need to understand how it worked then otherwise you won't be successful in the end.

- Marcus

Re: How to use raw headers in excel as drop box 2

Anonymous — Mon, 07 Sep 2015 13:34:11 GMT

I assure you I have no problem with linking tables. Data is well connected see below.

My problem started with CrossTable.

I only want to use excel headers as drop list. while using one source file with several tabs.

Re: How to use raw headers in excel as drop box 2

marcus_sommer — Mon, 07 Sep 2015 14:32:18 GMT

I'm not sure of it - to have an association between tables which didn't caused synthetic keys meant not that the associations are in this way which you need them. I think you will need rather some kind of composite key like:

...

[Month Col] & '|' & [Type] as Key

...

- Marcus

Re: How to use raw headers in excel as drop box 2

Anonymous — Tue, 08 Sep 2015 06:20:29 GMT

I have no idea Marcus, thanks anyway.

Please anyone can help my with this issue !!

Re: How to use raw headers in excel as drop box 2

marcus_sommer — Tue, 08 Sep 2015 10:25:37 GMT

Sorry, I couldn't help you with modelling your data-structures. It would need to have a deeper analyzing look into your data and which output is needed.

Therefore my suggestion is more general - I do often if I don't know the data-structure draw the tables and fields an their possible connections on white paper and write down the requirements and which information I already have and try to bring this in an order which I could understand - and this before I begin to load data and transform them. By complex requirements it's very helpful to have a plan how should it look like before you starts then to use trial and error.

- Marcus

Re: How to use raw headers in excel as drop box 2

Anonymous — Wed, 09 Sep 2015 13:11:35 GMT

Please anyone can help on this issue !!??

Re: How to use raw headers in excel as drop box 2

Anonymous — Wed, 07 Oct 2015 08:32:51 GMT

No received replies until now regarding this topic !!