Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
stevelord
Valued Contributor

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

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;

2 Replies
Not applicable

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.

stevelord
Valued Contributor

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

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;

Community Browser