Link 2 Sheets using common Fields

[erric3210]

Hi Community,

I've 2 tables: Table 1 & Table 2
For understanding purpose I've used fewer fields in sample data.

Data Source:

Table1:
LOAD *, Date_1&'$'&Category_1 AS Key1 INLINE [
Date_1, Category_1, Account_1
2022-03, Car, ABC-XY
2022-03, Bus, ABC-XA
];

Table2:
LOAD *, Date_2&'$'&Category_2 AS Key1 INLINE [
Date_2, Category_2, Account_2, Net_Amount, Budget
2022-03, Car, ABC-XX, 1000, 4000
2022-03, Car, ABC-XY, 2000, 5000
2022-03, Bus, ABC-XU, 3000, 6000
2022-02, Car, ABC-XB, 6000, 8000
2022-03, Van, ABC-XC, 7000, 9000
];

Here is my requirement:

Year & Category filters should be common filters (list boxes) and on their selections it should
give results as per data in Table 1 (which is in Sheet1) & Table 2 (which is in Sheet 2).
The selections should work on both sheets and generate data without any loss.

I've created composite key using fields Date&'$'&Category but the problem is when
I select Date_1 then only the matching records will filter out in Table_2 and I see the data discrepancy.

To fix this issue I've used subfield function like this:
Subfield(Date&'$'&Category,'$',1) for date & Subfield(Date&'$'&Category,'$',2) for category

This approach fixes the issue but I want to ask you guys is this the right approach or not.

else, is there any other way to achieve the output.

Need your suggestions.

Regards,

Erik

[Digvijay_Singh]

There could be multiple ways to handle this situation, Two of them could be like below if it suits your case -

1. You may concatenate both the tables and rename Date_1 and Date_2 to same name as Date, rename category_1 and category_2 to category in both the tables, this way your date and category filter will have all the dates and category, you would also create new field record_type('table1'/'table2') in both the tables to identify if a data row belongs to table 1 or table 2. This may be useful in set analysis expression in case you would need to filter data from a single table.

Data Source:

Table1:
LOAD *, Date_1 as Date, Category_1 AS Category, 'Table1' as Record_type INLINE [
Date_1, Category_1, Account_1
2022-03, Car, ABC-XY
2022-03, Bus, ABC-XA
];

Concatenate(Table1)
OAD *, Date_2 as Date, Category_2 AS Category, 'Table2' as Record_type INLINE [
Date_2, Category_2, Account_2, Net_Amount, Budget
2022-03, Car, ABC-XX, 1000, 4000
2022-03, Car, ABC-XY, 2000, 5000
2022-03, Bus, ABC-XU, 3000, 6000
2022-02, Car, ABC-XB, 6000, 8000
2022-03, Van, ABC-XC, 7000, 9000
];

2. You can create a link table between table1 and 2, it would have date and category field and a composite key from date and category to associate this link table with table 1 and table2, all three tables table1, table2 and link table will have this composite table. Keep Date and category fields only in link table.

https://community.qlik.com/t5/QlikView-App-Dev/what-is-link-table-how-it-works-and-its-usage/m-p/103...

Thanks,