2 Replies Latest reply: Jun 11, 2014 2:17 PM by Steve Lord RSS

    anything less taxxing than if(wildmatch()) functions in script?

    Steve Lord

      I've got this script to pivot stuff because I want it grouped by user id and generic loads tend to give sideways results.  I crashed my dashboard somewhere during the below activity.  When I commented out many of the formulas, the script went through okay again.  But I want all this stuff to pivot.  Any idea on the least demanding way to do this from the qlikview server perspective?

       

      1> setup a separate little qlikview just for this, store into qvd, and load the new fields into the main dashboard?

      2> some other expressions, maybe more explicit matching?

      3> syntax error or something in particular below that I could just tweak to cut the burden?

       

      (This is me trying to standardize some data from a wonky 10 million or so records populated by some old and new clients.  New clients are more standardized now, but old clients are pretty big and vary.)

       

      UserAttributesPivoted:

      LOAD UserId,

          Only(if((AttributeName='Relation' OR AttributeName='RELATION' or AttributeName='Relationship' or AttributeName='RelationType') AND not isnull(AttributeValue), AttributeValue, Null())) as [Attribute Relation],

          Only(if(wildmatch(AttributeName,'Date*Birth','DATE*BIRTH') and AttributeValue>0, Date(Floor(AttributeValue)), Null())) as [Date Of Birth],

          Only(if(wildmatch(AttributeName,'Custom*Attribute*1', 'CUSTOM*ATTRIBUTE*1') AND not isnull(AttributeValue), AttributeValue, Null())) as [Custom Attribute1],

          Only(if(wildmatch(AttributeName,'Custom*Attribute*2', 'CUSTOM*ATTRIBUTE*2') AND not isnull(AttributeValue), AttributeValue, Null())) as [Custom Attribute2],

          Only(if(Wildmatch(AttributeName, 'Elig*Start*Date', 'ELIG*START*DATE')AND AttributeValue>0, Date(AttributeValue), Null())) as [Eligibility Start Date]

      //    Only(if(Wildmatch(AttributeName, 'Elig*End*Date', 'ELIG*END*DATE')AND AttributeValue>0, Date(AttributeValue), Null())) as [Eligibility End Date],

      //    Only(if(Wildmatch(AttributeName, 'Date*Hire', 'Hire*Date')AND AttributeValue>0, Date(AttributeValue), Null())) as [Date of Hire],

      //    Only(if(Wildmatch(AttributeName, 'Base*Region', 'BASE*REGION')AND not isnull(AttributeValue), AttributeValue, Null())) as [Base Region],

      //    Only(if(Wildmatch(AttributeName, 'Region', 'REGION')AND not isnull(AttributeValue), AttributeValue, Null())) as Region,

      //    Only(if(Wildmatch(AttributeName, 'Subscribed*N*')AND not isnull(AttributeValue), AttributeValue, Null())) as [Subscribed Number],

      //    Only(if(Wildmatch(AttributeName, 'Ben*Type', 'BEN*TYPE')AND not isnull(AttributeValue), AttributeValue, Null())) as [Benefit Type],

      //    Only(if(Wildmatch(AttributeName, 'Gr*Mumber', 'Gr*Nmbr')AND not isnull(AttributeValue), AttributeValue, Null())) as [Group Number],

      //    Only(if(Wildmatch(AttributeName, 'Gr*Mumber*2', 'Gr*Nmbr*2')AND not isnull(AttributeValue), AttributeValue, Null())) as [Group Number 2],

      //    Only(if(Wildmatch(AttributeName, 'Z*C', 'Z*c', 'Zip', 'PartnerZip')AND not isnull(AttributeValue), AttributeValue, Null())) as [Zip Code]

          FROM

      C:\ProgramData\QlikTech\Documents\Data\UserAttributes.qvd

      (qvd) Group by UserId;

        • Re: anything less taxxing than if(wildmatch()) functions in script?

          Hi,

           

          Can you share an reduced qvd or your data and an example of result you spect?

           

          Best regards.

            • Re: anything less taxxing than if(wildmatch()) functions in script?
              Steve Lord

              Thanks, but I figured out a way to optimize.  Basically I just made a where statement out of those expressions to have the script select only the records to be pivoted, then to load those.  The data table was 10 million rows with a few hundred values in the attributename field, and I needed only 30 or so of the attributenames to be pivoted,

               

              Options were to whittle down the selection, or get a bigger server, and I was wondering if a third option would be some kind of expresion that was friendlier than the only(if(wildmatch())).

               

              I can't share the dataset and lack time to create a small model of the dataset, but here's the script that got everythng I needed through the RAM I have available to me:

               

              UserAttributesPivoted:

              LOAD UserId,

                  Only(if((AttributeName='Relation' OR AttributeName='RELATION' or AttributeName='Relationship' or AttributeName='RelationType') AND not isnull(AttributeValue), AttributeValue, Null())) as [Attribute Relation],

                  Only(if(wildmatch(AttributeName,'Date*Birth','DATE*BIRTH') and AttributeValue>0, Date(Floor(AttributeValue)), Null())) as [Date of Birth],

                  Only(if(wildmatch(AttributeName,'Custom*Attribute*1', 'CUSTOM*ATTRIBUTE*1') AND not isnull(AttributeValue), AttributeValue, Null())) as [Custom Attribute1],

                  Only(if(wildmatch(AttributeName,'Custom*Attribute*2', 'CUSTOM*ATTRIBUTE*2') AND not isnull(AttributeValue), AttributeValue, Null())) as [Custom Attribute2],

                  Only(if(Wildmatch(AttributeName, 'Elig*Start*Date', 'ELIG*START*DATE')AND AttributeValue>0, Date(AttributeValue), Null())) as [Eligibility Start Date],

                  Only(if(Wildmatch(AttributeName, 'Elig*End*Date', 'ELIG*END*DATE')AND AttributeValue>0, Date(AttributeValue), Null())) as [Eligibility End Date],

                  Only(if(Wildmatch(AttributeName, 'Date*Hire', 'Hire*Date')AND AttributeValue>0, Date(AttributeValue), Null())) as [Date of Hire],

                  Only(if(Wildmatch(AttributeName, 'Base*Region', 'BASE*REGION')AND not isnull(AttributeValue), AttributeValue, Null())) as [Base Region],

                  Only(if(Wildmatch(AttributeName, 'Region', 'REGION')AND not isnull(AttributeValue), AttributeValue, Null())) as Region,

                  Only(if(Wildmatch(AttributeName, 'Subscribed*N*')AND not isnull(AttributeValue), AttributeValue, Null())) as [Subscribed Number],

                  Only(if(Wildmatch(AttributeName, 'Ben*Type', 'BEN*TYPE')AND not isnull(AttributeValue), AttributeValue, Null())) as [Benefit Type],

                  Only(if(Wildmatch(AttributeName, 'Gr*Mumber', 'Gr*Nmbr')AND not isnull(AttributeValue), AttributeValue, Null())) as [Group Number],

                  Only(if(Wildmatch(AttributeName, 'Gr*Mumber*2', 'Gr*Nmbr*2')AND not isnull(AttributeValue), AttributeValue, Null())) as [Group Number 2],

                  Only(if(Wildmatch(AttributeName, 'Z*C', 'Z*c', 'Zip', 'PartnerZip')AND not isnull(AttributeValue), AttributeValue, Null())) as [Zip Code]

              FROM

              Data\Attributes2.qvd

              (qvd)Where (

              (wildmatch(AttributeName,'Date*Birth','DATE*BIRTH') and AttributeValue>0) OR

              ((AttributeName='Relation' OR AttributeName='RELATION' or AttributeName='Relationship' or AttributeName='RelationType') AND not isnull(AttributeValue)) OR

              (wildmatch(AttributeName,'Custom*Attribute*1', 'CUSTOM*ATTRIBUTE*1') AND not isnull(AttributeValue)) OR

              (wildmatch(AttributeName,'Custom*Attribute*2', 'CUSTOM*ATTRIBUTE*2') AND not isnull(AttributeValue)) OR

              (Wildmatch(AttributeName, 'Elig*Start*Date', 'ELIG*START*DATE')AND AttributeValue>0) OR

              (Wildmatch(AttributeName, 'Elig*End*Date', 'ELIG*END*DATE')AND AttributeValue>0) OR

              (Wildmatch(AttributeName, 'Date*Hire', 'Hire*Date')AND AttributeValue>0) OR

              (Wildmatch(AttributeName, 'Base*Region', 'BASE*REGION')AND not isnull(AttributeValue)) OR

              (Wildmatch(AttributeName, 'Region', 'REGION')AND not isnull(AttributeValue)) OR

              (Wildmatch(AttributeName, 'Subscribed*N*')AND not isnull(AttributeValue)) OR

              (Wildmatch(AttributeName, 'Ben*Type', 'BEN*TYPE')AND not isnull(AttributeValue)) OR

              (Wildmatch(AttributeName, 'Gr*Mumber', 'Gr*Nmbr')AND not isnull(AttributeValue)) OR

              (Wildmatch(AttributeName, 'Gr*Mumber*2', 'Gr*Nmbr*2')AND not isnull(AttributeValue)) OR

              (Wildmatch(AttributeName, 'Z*C', 'Z*c', 'Zip', 'PartnerZip')AND not isnull(AttributeValue))

              )

              Group by UserId;

               

              DIRECTORY;

              store UserAttributesPivoted into Data\UserAttributesPivoted.qvd;