topic Re: How to load a mapping table from SQL into QlikView? in QlikView

How to load a mapping table from SQL into QlikView?

QlikToFindOut — Wed, 05 Jul 2017 18:26:07 GMT

Hello,

I currently have these three tables in SQL:

QVW_Files

QVW_FileID

QVW_FileName

QVD_Files

QVD_FileID

QVD_FileName

GeneratedByQVW

There is then a mapping table that associates the two:

Mapping_QVW_QVD:

QVW_FileID

QVD_FileID

So I am trying to create a QlikView app that shows which QVD is generated by which QVW and also show when a QVD is being read by a QVW. So I've just labeled them "Generated" and "Consumed" respectively.

However, I'm having issues displaying them both because their column names are alike. Therefore, it creates a synthetic key and only allows me to show one relationship, and it happens to be the "Generated" relationship. If I create list boxes and select a particular QVD, the QVW list box would only show me the QVW that generates that QVD, and now the QVWs that consume that QVD.

Is there a way to get around this, or am I out of luck?

Thanks.

To be more specific, I'm looking to be able to have two list boxes. If I select a particular QVD, it would show me in one list box, the QVW that generates it, and in another list box, the QVWs that reference it.

For the QVD and "Generated" list box, it would be a 1 to 1 relationship.

For the QVD and the "Consumed" list box, it would be a 1 to many relationship.

This is how I'm bringing them in QlikView:

QVW_Files:

SQL SELECT

"QVW_FileID"

, "QVW_FileName"

FROM "QlikFiles".dbo."QVW_Files";

QVD_Files:

SQL SELECT

"QVD_FileID"

, "QVD_FileName"

, "GeneratedByQVW"

FROM "QlikFiles".dbo."QVD_Files";

Re: How to load a mapping table from SQL into QlikView?

juan_patrick — Wed, 05 Jul 2017 18:31:56 GMT

Hi leon!

I think you should have a third field in qvw_files table, it have to be the id of the qvd that generate and, with this field you can join with the qvw_files.

Juan Patricio

Re: How to load a mapping table from SQL into QlikView?

QlikToFindOut — Wed, 05 Jul 2017 18:34:11 GMT

Hi Juan,

Thanks, but isn't that what I'm already doing with the QVDs table? I don't have a problem getting the "Generated" list box to work. My problem is currently the "Consumed" list box.

Re: How to load a mapping table from SQL into QlikView?

juan_patrick — Wed, 05 Jul 2017 18:39:42 GMT

Leon,

I understand that your problem is the join between the tables to show data.

Another option to see is the hierarchy function:

Hierarchy in QlikView

If you want to show which QVD is reading by wich QVW, you have to join and I dont see the field to join.

Re: How to load a mapping table from SQL into QlikView?

Anonymous — Wed, 05 Jul 2017 18:58:07 GMT

Not sure if this is causing your issue but in your script you are giving both tables the same name QVW_Files: That will give you a problem.

Set the 2nd one to be QVD_Files.

Re: How to load a mapping table from SQL into QlikView?

QlikToFindOut — Wed, 05 Jul 2017 19:52:10 GMT

Sorry, that was a typo. It's actually named: QVD_Files.

Re: How to load a mapping table from SQL into QlikView?

Anonymous — Wed, 05 Jul 2017 19:58:00 GMT

I thought that might be the case.

But looking at your script, none of the fields are names the same so how are you getting a synthetic join?

Can you provide a picture of your data model?

And the script that creates your tables and your mapping table?