Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Tell us which business and trade publications you read most regularly: RESPOND NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
maelafifi
Contributor III
Contributor III

Link Tables without "joining" them

Hi All,

I have two tables and I am trying figure out how to link them without joining them. The problem with joining them is that each table has no distinct identifier to the other. For example, dates in the below tables will more than likely never match up (and they shouldn't). Also, if I join by customer from the data load, I will get repeated records, as the two tables combined would create an n:m relationship.

Essentially, what I would like to see is something that gives me total QTY Shipped for any given month as well as the count of Non-Conformance for that same given month, and if I want to get more granular, I should be able to filter on a customer and view the same data, only it should be reduced to just the customer. Below is an example

Orders/Ship Table

Date ShippedCustomerQTY Shipped
1/10/2018124
1/11/2018212
1/12/201834
1/13/201841

and Non-Conformance Table

NC DateCustomer
1/11/20182
1/13/20181
1/14/20183
1/17/20184

Expected out, total:

Year/MonthQTY ShippedNC Count
01/2018414

and if I filter by a given customer, customer 1 for example:

Year/MonthQTY ShippedNC Count
01/2018241

Any help would be much appreciated. I've been working on a solution on and off for a couple of weeks, now, and I just haven't been able to figure this out.

Thanks in advance,

Mohamed

1 Solution

Accepted Solutions
ychaitanya
Creator III
Creator III

1.Create a Master Calendar for all the Dates possible.

2. Rename the fields/ add the fields named dim_Date in all the tables so that it associates to the Calendar table.

Once you do the above steps, i don;t see ANY reason to get the answers for your both expected Outputs

Else

Concatenate both the table AS is and use the Distinct Qualifier in your expressions in right manner ..

Hope this should help.

Thanks

CY

View solution in original post

2 Replies
ychaitanya
Creator III
Creator III

1.Create a Master Calendar for all the Dates possible.

2. Rename the fields/ add the fields named dim_Date in all the tables so that it associates to the Calendar table.

Once you do the above steps, i don;t see ANY reason to get the answers for your both expected Outputs

Else

Concatenate both the table AS is and use the Distinct Qualifier in your expressions in right manner ..

Hope this should help.

Thanks

CY

maelafifi
Contributor III
Contributor III
Author

Thanks Chaitanya,

I've actually done this before, but because it has been a while and my job focuses more on software development, this technique seemed to skip past me when this task was requested! Worked like a charm!