Create calculated dimension in load script from an associated table in data manager?

[garyhowjtc]

hi, need some advice.

I have 2 tables which I associated using the data manager.

Now I created a dimension with nested if else custom logic and that is making the chart loading super slow.

How can I move this calculated dimension to the load script?

Appreciate any advice and simple examples.thank u..

‌

[rubenmarin]

Hi Gay, what's the expression?

It needs fields from both tables? If only one is need the same logic can be applied using data manager.

If it need from both tables some modifications in script are neded.

[garyhowjtc]

‌hi Ruben

Yes it needs fields from 2 tables. Let me try explaining..

E.g.

Table A

Year, Product, Current Amount

2017, Bike, $1

Table B

Year, Product, Prior Amount

2016, Bike, $2

then I have a calculated dimension now if multiple level of nested logic that categorize the type of sales based on a few conditions,

And 1 of the IF condition statements check if the variance I.e.Prior amount - Current Amount is negative or positive.

If negative, it will be classified as decreasing price sales or sort..

So I wish to move this dimension custom logic to the load script but I can't figure out how to do it because the 2 tables are associated using data manager

[Digvijay_Singh]

Can you share expression, may be it can be improved by some means. You may want to know below rules when it requires manual intervention to data manager generated script -

You can add script sections and develop code that enhances and interacts with the data model created in Data manager, but there are some areas where you need to be careful. The script code you write can interfere with the Data manager data model, and create problems in some cases, for example:

• Renaming or dropping tables added with Data manager in the script.
• Dropping fields from tables added with Data manager.
• Concatenation between tables added with Data manager and tables loaded in the script.
• Using the Qualify statement with fields in tables added with Data manager.
• Loading tables added with Data manager using Resident in the script.
• Adding script code after the generated code section. The resulting changes in the data model are not reflected in Data manager.

https://help.qlik.com/en-US/sense/November2017/Subsystems/Hub/Content/LoadData/managing-data.htm

[garyhowjtc]

‌Thank you, both. I have looked through those materials and scoured through the forums. Here is my expression if you would:

=if(

num([Contract Start Date.CAL.Date]) < $(vECGStartDate)

AND

SubStringCount([Contract No],'A5')<>0

,

'Group F: Billings for ECG (Jan 18)'

,

if(

num([Contract Start Date.CAL.Date]) >= $(vFY17StartDate) AND num([Contract Start Date.CAL.Date])<= $(vFY17EndDate)

AND

(

SubStringCount(UPPER([Process]),'CONVERSION FROM') <> 0

OR

SubStringCount(UPPER([Process]),'ASSIGNMENT FROM') <> 0

OR

SubStringCount(UPPER([Process]),'TRANSFER FROM') <> 0

)

,

'Group B1: New Offer for Assignment/Transfer/Conversion'

,

if(

num([Dismantling Date.CAL.Date]) >= $(vFY17StartDate) AND num([Dismantling Date.CAL.Date])<= $(vFY17EndDate)

AND

(

SubStringCount(UPPER([Process]),'CONVERTED TO') <> 0

OR

SubStringCount(UPPER([Process]),'ASSIGNMENT TO') <> 0

OR

SubStringCount(UPPER([Process]),'TRANSFER TO') <> 0

)

,

'Group B2: Termination for Assignment/ Transfer/ Conversion'

,

if(

num([Contract Start Date.CAL.Date]) >= $(vFY16StartDate) AND num([Contract Start Date.CAL.Date])<= $(vFY16EndDate)

AND

(

Index([Contract No],'A')=1 AND SubStringCount([Contract No],'-')<>0

OR

(Index([Contract No],'H')=1 OR Index([Contract No],'W')=1 AND Index([Contract No],'R') = 9)

)

AND

[Amount] <> [Prior Year Amount]

,

'Group C: Renewal (Staggered Rent Yr 1)'

,

if(

num([Dismantling Date.CAL.Date]) >= $(vFY17StartDate) AND num([Dismantling Date.CAL.Date])<= $(vFY17EndDate)

,

'Group 😧 Termination in current period'

,

if(

[Amount] <> [Prior Year Amount]

,

'Group E1: Rate Change (Rental Revision, Staggered Rent Yr2 & 3 Cases)'

,

if(

num([Contract Start Date.CAL.Date]) >= $(vFY16StartDate) AND num([Contract Start Date.CAL.Date])<= $(vFY16EndDate)

AND

(

Index([Contract No],'A')=1 AND SubStringCount([Contract No],'-')=0

OR

(Index([Contract No],'H')=1 OR Index([Contract No],'W')=1 AND Index([Contract No],'R') <> 9)

)

,

'Group E2: New Offer in prior period'

,

if(

num([Dismantling Date.CAL.Date]) >= $(vFY16StartDate) AND num([Dismantling Date.CAL.Date])<= $(vFY16EndDate)

,

'Group E3: Termination in prior period'

,

if(

num([Contract Start Date.CAL.Date]) >= $(vFY17StartDate) AND num([Contract Start Date.CAL.Date])<= $(vFY17EndDate)

AND

(

Index([Contract No],'A')=1 AND SubStringCount([Contract No],'-')=0

OR

(Index([Contract No],'H')=1 OR Index([Contract No],'W')=1 AND Index([Contract No],'R') <> 9)

)

,

'Group A: New Offer in current period'

,

if(

[GL Account Code]='6800101' OR [GL Account Code]='6400002' OR [GL Account Code]='6400009'

,

'Group G1: Billing for Inter-agency Project Grant/ Agency Fee'

,

if(

[GL Account Code]>=6800011 AND [GL Account Code]<=6800019

,

'Group G2: Billing for Sundry Income'

,

'Group H: Others (e.g. Full billing prior year but current yr not in full billing)'

)

)

)

)

)

)

)

)

)

)

)

[rubenmarin · Accepted solution]

Hi Gary, in script editor, after the automatic script, add a new section, this way you leave the auto part as it is and add modifcations after data manager script.

You can create a table that joins both tables, as an example it can be:

tmpData:

Noconcatenate

LOAD Year, Product, [Current Amount]

Resident [table A];

Join (tmpData)

LOAD Year+1 as Year, Product, Year as PriorYear, [PriorAmount]

Resident [table B];

DROP Table [table A];

DROP Table [table B];

// create new fields using fields from both tables

Data:

LOAD *,

          expression as newField

Resident tmpData;

DROP Table tmpData;

This counts that there is only one row by year and product, if there are multiple rows it can create duplicated rows.

[garyhowjtc]

‌Thanks Ruben. Just trying to understand if there is a way we can create the new field using the associated table but not additional joins.

[rubenmarin]

Mapping is also an option to pass values between tables:

mapPriorYear:

Mapping LOAD Year&Product

     [Prior Amount]

Resident TableB;

Rename TableA to tmpTableA;

TableA:

LOAD *,

     Applymap('mapPriorYear',(Year-1) & Product) as [Prior Amount]

Resident tmpTableA;

DROP Table tmpTableA