5 Replies Latest reply: Oct 20, 2014 11:31 PM by jagan mohan rao appala RSS

    Residential Load Issue

      I was wondering how I can reference columns from other tables within a residential load.

       

      In the code below, i can’t reference NotifType, Task Code, System or BU because they’re not a part of the DMS table.

       

      FunctLocTmp2:
      Load FunctLoc,
      If ([Document Type] = 'PMS Checklist',0,1) as [Not Submitted]
      Resident DMS
      where [NotifType]='MM' and [Task Code]='OSR' and (System<>'DYNACAD DIGITAL IMAGING WS' or System<>'HEMATEK' or System<>'PFA 100' or System<>'PFA 200' or System<>'BFT II') and BU<>'SY'
      ;

       

      What is the best way around this?

        • Re: Residential Load Issue
          Jyothish KC

          Hi Matt,

           

          I guess try to join the DMS table with Other table which contain the reference fields (NotifType, Task Code, System or BU) say  Table1. Then try to do resident Table1.

          Hope it may work.

           

          Regards

          KC

            • Re: Residential Load Issue

              The fields are scattered throughout my data model. I don't want to resort to that.

               

               

              Thanks for the suggestion though!

                • Re: Residential Load Issue
                  Jyothish KC

                  Hi Matt,

                   

                  Then why dont you try to do a set analysis in the front end instead of a where clause in the backend.

                   

                  In your chart you expression like:

                   

                  Sum({<[NotifType]={'MM'}, [Task Code]={'OSR'} >}Yourfield)

                   

                  Hope it may do the trick.

                   

                  Regards

                  KC

                  • Re: Residential Load Issue
                    jagan mohan rao appala

                    Hi Matt,

                     

                    Without join it is not possible in script, if you don't want to join the tables, then you can handle this in front end using Set Analysis like below

                     

                    Sum({<[NotifType]={'MM'}, [Task Code]={'OSR'}, System-={'DYNACAD DIGITAL IMAGING WS', 'HEMATEK', 'PFA 100', 'PFA 200', 'BFT II'}, BU -={'SY'} >} MeasureName)

                     

                    Note : Replace MeasureName with you actual field name you want to aggregate.

                    The above expression will be shown as syntax error but it works.

                     

                    Hope this helps you.

                     

                    Regards,

                    Jagan.

                • Re: Residential Load Issue
                  jagan mohan rao appala

                  Hi Matt,

                   

                  You need to join tables and then you need to filter using where clause.  Try like this

                   

                  Data:

                  LOAD

                  *

                  FROM TableName1;

                   

                  INNER JOIN (Data)

                  LOAD

                  *

                  FROM TableName2

                  where [NotifType]='MM' and [Task Code]='OSR' and (System<>'DYNACAD DIGITAL IMAGING WS' orSystem<>'HEMATEK' or System<>'PFA 100' or System<>'PFA 200' or System<>'BFT II') and BU<>'SY';

                   

                  Qlikview works like SQL even in SQL it is not possible, we join both the tables and then use where to filter the records.


                  Hope this helps you.


                  Regards,

                  Jagan.