topic Re: Scripting / Transformation Question in App Development

Scripting / Transformation Question

xyz_1011 — Tue, 23 Apr 2024 06:57:44 GMT

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!

Re: Scripting / Transformation Question

hic — Tue, 23 Apr 2024 07:23:02 GMT

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')

Re: Scripting / Transformation Question

xyz_1011 — Tue, 23 Apr 2024 07:27:58 GMT

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.

Re: Scripting / Transformation Question

hic — Tue, 23 Apr 2024 07:36:20 GMT

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.