Need help selecting from one table to same selection in another

[#1]

Hi, this is probably very easy, but I have a main table that I am loading, and then I am creating resident tables from that to populate various tables in my qlikview. What I want to do is when I select a value in one table, the other table will only display the same records.

//**********************************************************************

//Load data from TopClientsForQlikview into DetailImport

//**********************************************************************

DetailImport:     

LOAD LOB,

     ULTID,

     BAL_CLASS,

     PROD,

     CRDAY_BAL,

     PRDAY_BAL,

     DELTA,

     WEEK_BAL,

     MTD_BAL,

     CRDAY_DATE;

SQL SELECT LOB,

     ULTID,

     BAL_CLASS,

     PROD,

     CRDAY_BAL,

     PRDAY_BAL,

     PRDAY_BAL-CRDAY_BAL AS "DELTA",

     WEEK_BAL,

     MTD_BAL,

     CRDAY_DATE

FROM .dbo."TopClientsForQlikview";

//**********************************************************************

//Load data from DetailImport into TSISSummary

//**********************************************************************

TSISSummary:     

LOAD DetailImport.LOB,

     DetailImport.BAL_CLASS,

     DetailImport.PROD,

     SUM(DetailImport.CRDAY_BAL) AS 'CRDAY_BAL',

     SUM(DetailImport.PRDAY_BAL) AS 'PRDAY_BAL',

     SUM(DetailImport.PRDAY_BAL)-SUM(DetailImport.CRDAY_BAL) AS "DELTA",

     SUM(DetailImport.WEEK_BAL) AS 'WEEK_BAL',

     SUM(DetailImport.MTD_BAL) AS 'MTD_BAL'

RESIDENT DetailImport

GROUP BY DetailImport.LOB, DetailImport.BAL_CLASS, DetailImport.PROD;

//**********************************************************************

//Load data from DetailImport into SummaryByUltID

//**********************************************************************

SummaryByUltID:     

LOAD DetailImport.ULTID AS 'ULTID',

     SUM(DetailImport.CRDAY_BAL) AS 'CRDAY_BAL',

     SUM(DetailImport.PRDAY_BAL) AS 'PRDAY_BAL',

     SUM(DetailImport.PRDAY_BAL)-SUM(DetailImport.CRDAY_BAL) AS "DELTA"

RESIDENT DetailImport

GROUP BY DetailImport.ULTID;

//**********************************************************************

//Load data from DetailImport into SummaryByUltIDAndProduct

//**********************************************************************

SummaryByUltIDAndProduct:     

LOAD DetailImport.ULTID AS 'ULTID',

     DetailImport.PROD AS 'PROD',

     SUM(DetailImport.CRDAY_BAL) AS 'CRDAY_BAL',

     SUM(DetailImport.PRDAY_BAL) AS 'PRDAY_BAL',

     SUM(DetailImport.PRDAY_BAL)-SUM(DetailImport.CRDAY_BAL) AS "DELTA",

     SUM(DetailImport.WEEK_BAL) AS 'WEEK_BAL',

     SUM(DetailImport.MTD_BAL) AS 'MTD_BAL'//,

     //DetailImport.PROD AS 'Key'

RESIDENT DetailImport

GROUP BY DetailImport.ULTID, DetailImport.PROD;

When I add a table to the tab, I just drag over the columns from the resident table I want. However, when I click one table, where the column is in another table, I want those records selected. Am I on the right path here and if so, what am I missing? If not, how should I do this?

[#2]

Knowlton,

If I understand your question correctly, this is what Qlikview is known for.

I am assuming that you want a selection on one table to be applied on the other as long as they have common columns. right?

It should happen by default as long as the column names are same across the tables. You might want to check the Table viewer and ensure that the tables are joined on the same column names as expected. If not, probably, you will have to change the column names so that they match.

I hope this helps. if not could you please be more specific about your question?

Cheers,

[#3]

Hi Mallesh, thanks for responding.

Yes that is what I want to see happen. However, I am qualifying everything, as this report is going to be rather complicated, and I wanted to use resident tables to hold the many sub queries I will be producing for various table boxes. When I use the multi-selector box to see what the values are when I select, I can see that the qualified names appear, so SummaryByUltId.ULTID is different than SummaryByUltIDAndProduct.UltID, even though the data is the same. Its just that I am using one resident table to populate one chart and others for other charts. Is there a way to tell Qlikview to link these UltIDs even though the Qualifying adds the resident table name to the column name? When I look at the table viewer (Control-T), there are no relationships defined. And I don’t see a way of setting them like, lets say when you do in MS Access.

I would really hate to not use the resident table approach to this, because when I am building various tables and charts, I really don’t want to embed the logic in them, I think it would be easier to script them, and then drag and drop the columns I want since I will easily know from the Qualified names which belongs to what.

Thanks

[#4]

Well, I redid everything just using the main import table, getting rid of the residents, using Straight Table Charts, and everything does work like magic. But it means I have to build all of the expressions in the charts where I already had the data as I wanted in the residents, and could just drag and drop the columns I wanted as I build them in each table. What I have done works, but I would really like to know if I could still do what I was originally set out trying to do.

[#5]

Knowlton,

You can use Unqualify option to just unqualify the column you want to join on.

for example:

Unqualify ULTID;

LOAD * RESIDENT SummaryByUltId;

LOAD * RESIDENT SummaryByUltIDAndProduct;

(I am aassuming these are resident tables.)

If you have to have qualification for this column, you can try loading the same column twice in the same load statement with a different name:

LOAD ULTID as SummaryByUltID_ULTID,* RESIDENT SummaryByUltId;

LOAD ULTID as SummaryByUltIDAndProduct_ULTID, * RESIDENT SummaryByUltIDAndProduct;

I hope this makes sense,

Cheers