Building new fields in script without exporting/reimporting to/from excel

[stevelord]

Hi, I've asked this before and almost got the correct answer.  I am re-posing a simplified version of previous inquiry,

I want data from a data source that looks like this:

UserId     TestName     TestValue     TestDate

123          Systolic          120          1/25/2012         

123          Diastolic          80           1/25/2012

456          Systolic          140          2/13/2012

456          Diastolic          90           2/13/2012

To make a table box that looks like this:

UserId     BloodPressure     TestDate

123          120 / 80               1/25/2012

456          140 / 90               2/13/2012

Preferably using some sort of script to make the BloodPressure field.  (I can make the field, but can't get the number values into the same cells together.)  Thanks in advance!

Background information if needed:

I have this one field on a table called TestName.

In this field I have two values, Systolic and Diastolic.

Beside Systolic and Diastolic are related values for TestValue, Unit, TestDate, UserId etcetera.

I have tried to concatenate Systolic and Diastolic tests to make a single BloodPressure field but it is not working.

For instance with this formula in my script

if(TestName='Systolic',TestValue)&' / '&if(TestName='Diastolic',TestValue) as BloodPressure,

I get  ' / 80' and '120 / ' on two separate lines of my table box for user 123 on 1/25/2012 in the BloodPressure field.

[rustyfishbones]

Some sample data would be nice, can you add 2 lines to an excel spreadsheet and upload

[pbaenen]

You can do a generic load:

Generic Load

          *

          ;

Load * Inline [

UserId,TestDate,TestName,TestValue

123,1/25/2012,Systolic,120   

123,1/25/2012,Diastolic,80

456,2/13/2012,Systolic,140

456,2/13/2012,Diastolic,90

];

That'll make several tables, and you can piece them back together with a for loop if you need to store the data.

[rustyfishbones]

I had a similar problem, I just got help with it from http://community.qlik.com/people/gwassenaar

You need this at the End of Your Script

	JOIN
	LOAD
		UserId, CONCAT(TestValue,'/') as BloodPressure
	RESIDENT Test
	GROUP BY UserId;

I have attached the qvw

[#5]

try this

load

UserId,TestName, TestDate,

only(if(Previous(TestValue)>TestValue,Previous(TestValue)&'/'& TestValue)) as BloodPressure

Group by UserId,TestName, TestDate;

test:

LOAD * INLINE [

    UserId, TestName, TestValue, TestDate

    123, Systolic, 120, 1/25/2012

    123, Diastolic, 80, 1/25/2012

    456, Systolic, 140, 2/13/2012

    456, Diastolic, 90, 2/13/2012

];

In pivot chart

dimension -- UserId, TestDate

expression-- BloodPressure

then outpul like this

UserId	TestDate	BloodPressure
123	1/25/2012	120/80
456	2/13/2012	140/90

[pbaenen]

I found the reference:

http://qlikviewnotes.blogspot.com/2010/05/use-cases-for-generic-load.html

BP:

Generic Load

          *

          ;

Load * Inline [

UserId,TestDate,TestName,TestValue

123,1/25/2012,Systolic,120   

123,1/25/2012,Diastolic,80

456,2/13/2012,Systolic,140

456,2/13/2012,Diastolic,90

];

Let vFirstTableName = TableName(0);

BloodPressure:

Load Distinct

          UserId,

          TestDate

Resident $(vFirstTableName);

FOR i = 0 to NoOfTables()

  TableList:

  LOAD TableName($(i)) as Tablename AUTOGENERATE 1

  WHERE WildMatch(TableName($(i)), 'BP.*');

NEXT i

FOR i = 1 to FieldValueCount('Tablename')

  LET vTable = FieldValue('Tablename', $(i));

  LEFT JOIN (BloodPressure) LOAD * RESIDENT $(vTable);

  DROP TABLE $(vTable);

NEXT i

Drop Table TableList;

[stevelord]

For the inline load, would I be able to use some form of dynamic expression like if(TestName=Diastolic,testvalue) in lieu of specifying the actual value?  The dataset is actually more robust and has over a dozen testnames whose presence or absence depends on whether a user had the test, so I can’t specify specific value combinations or assume the diastolic is actually directly above the systolic.

My Excel solution involves exporting items with testname=systolic to one table from a tablebox, exporting items with testname=diastolic to another table (both with user ids, testdates, and units along with the testvalues) and basically reloading both from those worksheets and leftjoining the one to the other.  I made fields if(testname=systolic, testvalue) as systolic, and one for diastolic, to create these tableboxes, then exported/reimported to get them on the same lines and did a resident load and concatenate to make a blood pressure field.  Result of that is this:

UserId   BloodPressure             TestDate

123        120 / 80                      1/25/2012

123        120 / 80                      1/25/2012

456        140 / 90                      2/13/2012

456        140 / 90                      2/13/2012

My solution is doable for now since it’s only 270,000 or so rows for each table, but it takes a nice 5 minutes watching hourglasses and not responding messages for QV to load the table boxes I export from. ☺  We tried generic loads, and were able to collapse the tables back to a new table with some more script, but load times were ferocious and we weren’t smart enough to track down what kept the data from lining up right.  (It might have been we needed to make the units and/or some other fields testname specific as well, or find more things to leave out of the table loads, but we were crashing things where we left off and went another route.  Looking to upgrade to 16GB RAM laptops now. ☺)