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]
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;
