Adding new fileds from other table as dimensions to the main table

[krzys]

Hey,

I came across a new challenge that I cannot solve, but I bet will be easy to solve for more advanced users within the Qlik community. 

Some timestamps are stored outside the main opportunity table, i.e., stage change. Since this data is stored in a separate table, when I connect both tables with a KEY, all the stage changes are recorded across each opp several times. 

I would rather keep 1 line per 1 opportunity where the fields coming from the other tables are added as dimensions.

I have a sample of Table 1 and Table 2 + current result and Expected result in the attached file.

The loading script looks like this:

[Opportunity]:

 LOAD

[Id] as [%KEY_Opportunity ID],

    [CreatedDate] as [Opp Created Date],

    [StageName] as [Opp Stage]

FROM Opportunity;

[Opportunity History]:  

    LOAD 

OpportunityIdas [%KEY_Opportunity ID],

CreatedDateas [Opp Field Hist Created Date],  

NewValueas [Opp Field Hist New Value]

FROM OpportunityHistory;

Thanks,

Krzys

[stevejoyce]

First unpivot your [Opportunity History] table use generic load. I just gave an example on another question, please take a look at https://community.qlik.com/t5/App-Development/What-functions-in-data-load-script-should-I-use-on-the...

Once you do that, and have 1 row per KEY_Oppurtunity_ID, then your left join to Opportunity will keep only having 1 row per key value.

You can skip a step, by instead of creating a temporary unpivotted history table, within the for loop, join directly to opportunity table.  but it may be easier for you to trace through to first build the history table unpivoted then join *.

[krzys]

Thanks, @stevejoyce  Generic load looks like the thing I need here. I tried to follow the other examples but with no success. Is there a chance you could send me the load script to test. I would really appreciate it!

[stevejoyce · Accepted solution]

opp:
LOAD [%KEY_Opportunity ID],
[Opp Close Date Y-Q],
[Opp Stage],
[Opp Created Date]

FROM
[C:\Users\sjoyce\Desktop\Case Qlik Aug 2021.xlsx]
(ooxml, embedded labels, table is [Table 1])
where not isnull([%KEY_Opportunity ID])
;

opp_history:
generic LOAD [%KEY_Opportunity ID], [Opp Field Hist New Value], [Opp Field Hist Created Date]
;
load
[%KEY_Opportunity ID],
[Opp Field Hist Created By ID],
[Opp Field Hist Created Date],
[Opp Field Hist New Value]
FROM
[C:\Users\sjoyce\Desktop\Case Qlik Aug 2021.xlsx]
(ooxml, embedded labels, table is [Table 2]);

let vGenericName = 'opp_history';
let vNumberOfTables = NoOfTables();

For vTableNo = 0 to $(vNumberOfTables)

Let vTableNo_new = $(vNumberOfTables)-$(vTableNo);
Let vTableName = TableName($(vTableNo_new)) ;

Trace WORKING ON TABLE $(vTableNo_new) named $(vTableName);

If Subfield(vTableName,'.',1)='$(vGenericName)' Then

Trace CREATED IN GENERIC LOAD. JOIN AND DROP;

Left Join ('opp')
Load *
Resident [$(vTableName)];

Drop Table [$(vTableName)];

End If

Next vTableNo

SET vNumberOfTables =;

[krzys]

Thank you!