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

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

stevelord — Tue, 28 May 2013 21:23:56 GMT

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.

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

rustyfishbones — Tue, 28 May 2013 21:34:41 GMT

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

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

pbaenen — Tue, 28 May 2013 22:05:13 GMT

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.

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

rustyfishbones — Wed, 29 May 2013 12:48:59 GMT

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

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

Wed, 29 May 2013 12:59:45 GMT

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

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

pbaenen — Wed, 29 May 2013 13:05:48 GMT

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;

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

stevelord — Wed, 29 May 2013 15:37:05 GMT

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

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. ☺)