7 Replies Latest reply: Mar 7, 2018 11:17 AM by Ruben Marin RSS

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

    Gary How

      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..

        • Re: Create calculated dimension in load script from an associated table in data manager?
          Ruben Marin

          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.

          • Re: Create calculated dimension in load script from an associated table in 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

              • Re: Create calculated dimension in load script from an associated table in data manager?
                Gary How

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

                )

                )

                )

                )

                )

                )

                )

                )

                )

                )

                )