topic Re: Linking tables without association in QlikView

Linking tables without association

Anonymous — Thu, 11 Oct 2018 09:11:56 GMT

Hi all,

I am trying to link two table together while on purpose not having them join/associate.

Let me explain based on example below. I have two table (Table 1 and Table 2). Both have a project column with the same values in them. So obviously, I could have both tables join/associate in Qlikview and link them up. I have a specific reason that I don't want to do that so in order to avoid this to happen, I qualified Table 2 in the loading script.

Now that that is done, I do would like the hours from Table 2 to appear in Table 1. I was thinking I can maybe do this by capturing the project letter of Table 1 and use that in a set analysis to sum the hours but not sure how to do that. I tried this expression in Table 1 but it is not working:

=sum({<Table2.Project = {'Project'}>} Table2.Hours)

Any ideas? Many thanks in advance.

Re: Linking tables without association

swuehl — Thu, 11 Oct 2018 09:21:27 GMT

Maybe like

=sum({<Table2.Project = p(Project) >} Table2.Hours)

But this will not group the Table2.Project values if used in a chart with dimension Project.

You would need something like

=Sum( If(Table2.Project = Project, Table2.Hours))

This will create a cartesian join on runtime.

Re: Linking tables without association

Anonymous — Thu, 11 Oct 2018 10:16:26 GMT

Hi Stefan,

This works. Many, many thanks!

Danny

Re: Linking tables without association

Anonymous — Thu, 11 Oct 2018 12:18:54 GMT

Stefan,

One more quick question; I used your suggested formula to come to a solution:

=Sum( If(Table2.Project = Project, Table2.Hours))

However, I actually have two criteria to fulfil; the first one is Table2.Project = Project but I also have a second where Table2.Phase = Phase.

I adapted your formula to read like the one below, but rather than creating a smaller subset, it creates a much bigger one. Any thoughts?

=Sum( If(Table2.Project = Project, if(Table2.Phase = Phase, Table2.Hours)))

Thanks,

Danny

Re: Linking tables without association

swuehl — Thu, 11 Oct 2018 12:35:35 GMT

Well, not 100% sure. I usually avoid using these kind of unlinked tables.

Try maybe

=Sum( If(Table2.Project & '-' & Table2.Phase = Project &'-'& Phase, Table2.Hours))

Sum(

Aggr(

If (Table2.Project = Project and Table2.Phase = Phase, Table2.Hours),

Table2.Project, Table2.Phase, Project, Phase)

)

If one of these work, you can add set analysis to limit the possible combinations of the run time JOIN:

{<Table2.Project = p(Project), Table2.Phase = p(Phase) >}

Re: Linking tables without association

Anonymous — Thu, 11 Oct 2018 13:42:14 GMT

Thanks Stefan. but neither of the options worked. This is what I have (I used the real fields so I don't have to keep converting).

Any suggestions are welcome, in the meantime I'll keep looking.

=sum({$<[BUDGET.Expense Category]={'Labor - Client Service'}, BUDGET.PID = p(PID), [BUDGET.Work Phase] = p(Phase)>} if(BUDGET.PID&'-'&[BUDGET.Work Phase] = PID&'-'&Phase, BUDGET.FY19))

Thanks!

Re: Linking tables without association

swuehl — Thu, 11 Oct 2018 13:47:10 GMT

"Work Phase" and "Phase" show the same values?

Can you upload a simplified sample QVF or some screenshots?

Also a view of your data model (to see the tables involved) may help.

Re: Linking tables without association

Anonymous — Thu, 11 Oct 2018 13:55:16 GMT

Stefan,

Please find attached. Let me know if something else may be more helpful.

Re: Linking tables without association

swuehl — Thu, 11 Oct 2018 14:13:33 GMT

Ok, Project and Phase are part of a synthetic key and are located in two to three resident tables in your model.

I've seen some issues with large synthetic keys (and I am not sure what the run time cartesian JOIN will do in such a case).

Can't you concatenate your actual and forecast fact table to eliminate the huge syn key table?

Also, remove Month and Year from the fact table, creating a Year-Month field in the script and link this to your financial year calendar.

Re: Linking tables without association

Anonymous — Thu, 11 Oct 2018 18:05:30 GMT

Hi Danny

You can use ApplyMap to and could avoid this kind of issue

https://help.qlik.com/en-US/sense/June2018/Subsystems/Hub/Content/Scripting/MappingFunctions/ApplyMap.htm

Regards,

Re: Linking tables without association

Anonymous — Fri, 12 Oct 2018 07:07:50 GMT

Thanks Stefan and Emmanuel. It seems the best way to go is to join the table during the loading script. I'll give that a shot

Thanks again for the help