Pivot Table against Multiple Data Sources

[CyberCannon]

Howdy everyone,

Hope y'all are doing well and I appreciate any help in advance! 

Recently I have been interning with a company and have been trying to create a pivot table that utilizes two different tables that are loaded.  I have tried numerous ways in the function (Lots of if statements) but cannot get it to work correctly.  I am thinking I will have to create a joined table in the Data Load Editor, but am unsure about the syntax/best method in Qlik Sense.

The two tables, (A and B) are have many different fields, but I only want to add the relevant information to keep table size down since the data sources are very large. Below are examples of what data I want in the joined table - I have excluded most of the other fields that are not relevant.

Table A
Structure, Quantity, A_MonthYear

Table B
Type, Quantity, B_MonthYear

What I would like to have for the joined table (C) is the following:

Table C
Type, Structure, Quantity, C_MonthYear

If possible, I want to only take certain Types and Structures and actually put them into 1 column, but just getting this idea to work would be a nice first step haha.

I feel like this is possible in QS, and I know I am a bit over my head with this project, but was hoping to see if you all had any insight on how to accomplish this.

Thanks again everyone!

[stevejoyce]

Do you want to manually put this together in excel?  It doesn't sound too difficult but i'm not totally clear.

It doesn't make sense to me that you'd have a Quantity for Type and a Quantity for Structure, and your final table would have only 1 Quantity column.  You'd either want a Type - Quantity and Structure - Quantity (joining tables) or Quantity would be null for Type when loaded from table A and null for structure when loading from table B (concatenating tables).    

But if you mockup in excel your 2 tables, and what you want to display on the front-end that will be good.

[brunobertels]

Hi 

if you want to get Table C you will need to join them with a unique KEY , that's are the same in actual table A and B. 

Does field "Month year" could be the unique key betweens the 2 tables A and B ?

if not you will get sometink like this ( no relation between Type and Structure) 

TABLE C
Type	Structure	Quantity	CMonthYear
A	-	10	08-2021	(extract from Amonthyear)
B	-	20	08-2021	(extract from Amonthyear)
C	-	30	08-2021	(extract from Amonthyear)
-	1	10	08-2021	(extract from Bmonthyear)
-	2	20	08-2021	(extract from Bmonthyear)
-	3	30	08-2021	(extract from Bmonthyear)

is there a real relation between Type and structure ? 

[CyberCannon]

I am thinking that CMonthYear would be the key as well since it is the only field that is constant between tables.

The are no real relations between type and structure.  For example, I might want Type A & B with only Structure 3 listed in a final table.  The goal I am trying to do is to get the Quantity for A, B, and 3 for the CMonthYear into the pivot table

[stevejoyce · Accepted solution]

I'm guessing your thinking the pivot table to look like this?

If so, I would do this:

Table A

1 as FromStructure,

Structure,

Quantity as Structure_Quantity,

A_MonthYear as MonthYear

From datasource;

outer join (Table A)

Type,

Quantity as Type_Quantity,

B_MonthYear as MonthYear

resident Table B

;

drop table B;

Table_C:

if(FromStructure = 1, Structure_Quantity, Type_Quantity) as Quantity

,if(FromStructure = 1, 'Structure', 'Type') as Category

,if(FromStructure = 1, Structure, Type) as Category_Value

,MonthYear

,Type

,Structure

resident Table A

;

drop table Table A;

Then your pivot table row dimensions are Category and Category_Value, column dimension is MonthYear, and expression is Sum(Quantity).

[CyberCannon]

This definitely led me into the right direction.   Thank you so much Steve.  Have a great weekend!