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

[stevelord]

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]

Hi,

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

Best regards.

[stevelord]

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;