Hi, below is a sample bit of a massive columnar data file I am working with. Qlikview crashes when I try full generic loads, and doesn't get desired result when I cut generic load down to two of the ten testnames I need. (Other tables are cut to load only the few fields needed for each.) Pivot Table objects also crash, and file is one or two million rows so too big for transformation step.
I have tried the resident load/group by technique and get through it with a synthetic key but no other errors. It doesn't flatten the table as desired though. I have tried multiple small generic loads of a few fields at a time and crashed QlikView in fantastic ways. I've tried loading each separately and left joining to no avail. I am trying to get Systolic and Diastolic on the same row with each other, grouped by ClientAccountName, UserId, TestDate, and Source. I've used if statements to create separate Systolic and Diastolic fields, and added TestName to the group by fields, and used sum(TestValue) for my aggregation function. I've tried in-script concatenates, in-object aggr statements, something about changing axes on a straight table...
Syntax has come to be correct in all of these methods, but still I get a choice between crashing Qlikview, Diastolic and Systolic on separate rows with a value in one column and null in the other column, or all of the sys/dia combinations next to all of the dates, or various other weird combinations.
Nicest looking most recent failed attempt:
SUM(if(TestName='Systolic',TestValue)) as Systolic,
SUM(if(TestName='Diastolic',TestValue)) as Diastolic,
SUM(if(TestName='BMI',TestValue)) as BMI
[R:\Data Analytics\Sample QVDs\05.28.2013\Biometrics.qvd]
WHERE TestName='Systolic' OR TestName='Diastolic' OR TestName='BMI'
Group By UserId, Source, TestName, TestDate;
Now, could I put something into the return part of my if statements to make it return the testvalue where testname=X?
e.g. if(TestName='Systolic',if(TestName='Diastolic',TestValue)) as Diastolic //to get the Diastolic next to the Systolic? That formula doesn't actually work because it looks in systolic row to find a diastolic and there is nothing there, but a peek into the diastolic row from systolic or something like that maybe? (I'd need help on peek function syntax.) Help?