topic Re: Link Problems between excel sheets in QlikView

Link Problems between excel sheets

Thu, 13 Aug 2015 17:03:06 GMT

Hi,

I'm working on a qlikview desktop script. This script is loading from an excel sheet with 3 different sheets. I'm able to load the data, but the linking between the 3 sheets is not working properly.

I'm using a sheet called "Link" to link my first sheet with the the 3rd sheet.

Here is my script:

LOAD *
FROM

(ooxml, embedded labels);

LOAD *
FROM

(ooxml, embedded labels, table is Link)

LOAD *
FROM

(ooxml, embedded labels, table is People)

I see the corrolation between the first sheet and link happening. I don't see the happening between Link and People
.

ID between first sheet and second is DEV_ID and between second and 3rd is PERSON_ID.

Any help is greatly appreciated.

Re: Link Problems between excel sheets

sinanozdemir — Thu, 13 Aug 2015 17:49:46 GMT

Can you post the field names that these three sheets have?

Thanks

Re: Link Problems between excel sheets

maxgro — Thu, 13 Aug 2015 17:58:57 GMT

check the names and the case (upper / lower) of the fields you want use to associate:

same name and same case

Re: Link Problems between excel sheets

Thu, 13 Aug 2015 18:30:58 GMT

You want all of them or just the key?

Sheet 1 - DEV_ID

Sheet 2 - DEV_ID, PERSON_ID

Sheet 3 - PERSON_ID

Re: Link Problems between excel sheets

Thu, 13 Aug 2015 18:32:10 GMT

Hi,

I've copied and pasted the names from one field to another. They are all the same spelling/and case sensitivity.

Qlikview is seeing they as primary keys.

Re: Link Problems between excel sheets

sinanozdemir — Thu, 13 Aug 2015 18:38:38 GMT

By any chance, does DEV_ID also exist in Sheet 3? It might be concatenating if those are the fields that you have.

Re: Link Problems between excel sheets

Thu, 13 Aug 2015 18:42:50 GMT

Please make sure that the Field Names are common (case insensitive) on the Excel.

Please add all the field names to your Load statement so you can see if any difference field names.

P.S: Please post sample data to debug the issue.

Re: Link Problems between excel sheets

Thu, 13 Aug 2015 19:15:09 GMT

Hi Sinan,

It does not. Sheet 2 is supposed to do the coloration between DEV_ID and PERSON_ID.

Reason being is we have more people per device and I didn't want to have multiple redundant records.

Re: Link Problems between excel sheets

sinanozdemir — Thu, 13 Aug 2015 21:05:01 GMT

As dathu.qv suggested, it might be much easier if you could post a sample dataset.

Thanks

Re: Link Problems between excel sheets

Thu, 13 Aug 2015 21:45:58 GMT

Like, would you like the file itself or the excel sheet?

Re: Link Problems between excel sheets

sinanozdemir — Thu, 13 Aug 2015 22:13:57 GMT

Maybe excel sheets with a few rows in them if they are not confidential?

Re: Link Problems between excel sheets

Thu, 13 Aug 2015 22:51:59 GMT

This doesn't make sense. I created a new sheet with way less data and fake data to upload and I created a new qlik doc and its working. Let me strip down what I currently have and try uploading it since I don't want any confidential info.

Re: Link Problems between excel sheets

Thu, 13 Aug 2015 23:21:03 GMT

Ya. When I cut the data and change values it all of a sudden does what it should.

But with the current data, It doesn't work and I can't upload it

Re: Link Problems between excel sheets

Thu, 13 Aug 2015 23:24:55 GMT

I will say this. I see the coloration happening between the people sheet and the Link.

When I select a name, it links the PERSON_ID with the PERSON_ID on the Link Sheet and then it finds the DEV_ID on the LINK Sheet, but for some reason its not link with the Device Sheet. I thought I was going crazy so I grabbed the DEV_ID and searched for it in on excel and I find it.

If I remove the link sheet and put everything on one sheet it works just fine.

Is there a better way to do the linking any better?

Maybe put the person ID on the same sheet as the Device sheet as an array and then somehow explode it using a comma or something.

Example,

PersonID = Per1, Per2, Per3

and then explode that and use that to link with the Person Sheet?

Re: Link Problems between excel sheets

sinanozdemir — Thu, 13 Aug 2015 23:33:05 GMT

There could be a hidden character in the column name.

Re: Link Problems between excel sheets

Thu, 13 Aug 2015 23:37:00 GMT

I just used Excel's CLEAN function and changed the old column name to OLD. Same problem

Re: Link Problems between excel sheets

sinanozdemir — Thu, 13 Aug 2015 23:38:54 GMT

Do me a favor, copy the same column name from another sheet and paste it into the problematic column to see if it fixes anything.

Re: Link Problems between excel sheets

Thu, 13 Aug 2015 23:47:53 GMT

Same thing. But I discovered something. If I select the DEV_ID in qlikview it links everything together. Device info and people. But if I select the name while having the DEV_ID selected, it breaks. I deselected the DEV_ID same result. No workie. Let me try the clean for the name and try again.

Re: Link Problems between excel sheets

Thu, 13 Aug 2015 23:49:33 GMT

Didn't make a difference, Hmm. This is a little weird.

Re: Link Problems between excel sheets

sinanozdemir — Thu, 13 Aug 2015 23:56:36 GMT

At this point, It is a bit difficult to determine the cause of the problem without seeing some sample and it seems confidential. Although, I say this that sometimes I receive exact same files one from SQL Server and one from MySQL and the fields don't link due to hidden characters.

Try to upload some samples when you can.

Thanks