Scripting / Transformation Question

[xyz_1011]

Hi all,

i have the following two tables:

Contracts:

ContractID	Valid_From	Valid_To	Risk
123	2003-01-01	2003-12-31	A
123	2003-01-01	2003-12-31	B
123	2003-01-01	2003-12-31	C
123	2004-01-01	2004-12-31	B
123	2004-01-01	2004-12-31	E

Risks:

Risk	Risk_Name
A	Risk_A
B	Risk_B
C	Risk_C
D	Risk_D
E	Risk_E

I am seeking a way to transform the two table into one table with the following structure:

Result:

ContractID	Valid_From	Valid_To	Has_Risk_A	Has_Risk_B	Has_Risk_C	Has_Risk_D	Has_Risk_E
123	2003-01-01	2003-12-31	Yes	Yes	Yes	No	No
123	2004-01-01	2004-12-31	No	No	No	Yes	Yes

Of course the Contract table contains multiple ContractIDs with different periods. I am aiming for a result table that contains for every given Valid_From / Valid_To the information whether any of the possible risks is included.

Thanks in advance for some help here!

[hic]

No transformation is needed. Just load the first table, and then create a straight table with the following:

Dimensions: ContractID, Valid_From, Valid_To

Measures:
Has_Risk_A: If(Count({<Risk={A}>} ContractID)>0,'Yes','No')
Has_Risk_B: If(Count({<Risk={B}>} ContractID)>0,'Yes','No')
Has_Risk_C: If(Count({<Risk={C}>} ContractID)>0,'Yes','No')
Has_Risk_D: If(Count({<Risk={D}>} ContractID)>0,'Yes','No')
Has_Risk_E: If(Count({<Risk={E}>} ContractID)>0,'Yes','No')

[xyz_1011]

Thanks @hic (and great to have the pleasure again 🙂 ), but (and i should have said this) this will only work as long as the possible risks (in table Risks) dont change (i.e. a new risk is added). If that happens i'd need to add an additional measure to my table, correct ?

I would prefer to go the transform way and create a table as described in the Result table.

[hic · Accepted solution]

True.

For a real transform, you can use Generic Load. See https://community.qlik.com/t5/Design/The-Generic-Load/ba-p/1473470 

If you consider "Risk" as the Attribute and 'Yes' as the value, you would get pretty much what you want.