Need help pulling number values from 2 fields to use in new field in script

[stevelord]

Hi, I've created a couple of fields in my script that I want numerical results to be given.  Below is one approach I've tried but it doesn't work.  It should give a good idea of what I am going for though.  TestName and TestValue are the original field names, I'm just trying to extract the values and create a new field with statements rather than generic load.

if(TestName='Total Cholesterol' AND TestValue>0 AND TestValue<7000, NUM(TestValue)) as CholRatioTotal,
if(TestName='HDL' AND TestValue>0 AND TestValue<1000, NUM(TestValue)) as CholRatioHDL,

The fields show up after I load the script, but there is nothing populated in them.  I used Dual statements to create messages about whether other items are high or low risk if it falls in a certain numerical range, and those all work, I am just stuck trying to get this one to give me numerical values instead of a message.  (I took out the Dual function to try with regular if statements above.)

After I get numbers for CholRatioTotal and CholRatio HDL, I plan to do something like this for a new field in the script:

NewTable:

Load *,

if(CholRatioTotal / CholRatioHDL <=4.9, Dual('Normal',1),

if(CholRatioTotal / CholRatioHDL >4.9, Dual('At Risk, High',2)) as [Cholesterol Ratio]

Resident <oldtable>;

Drop table <oldtable>;

Thanks for help!

-Steve

[vivek_niti · Accepted solution]

Hi,

Try placing the TestValue inside brackets...

Num(If(TestName = 'Total Cholesterol' AND (TestValue>0 AND TestValue <7000),TestValue) AS CholRatioTotal.

Regards,

[stevelord]

Thanks, that worked like a charm technically and the values now show up on my tables, so I gave it a correct answer.

Below is an issue I came to after turning that corner.  I will post separately, but posting here in case you know this off the cuff as well.   (You can answer in the other post for more correct answer credit.)

The Total Cholesterol and the HDL values are on two separate lines under the TestValue heading in the original data, so when I try to divide one by the other it only picks up a null beside it and gives error or 1 or some total cholesterol value depending on the formula I try.

Source data and data on the Qlikview straight table looks like

ClientAccountName ExternalID TestDate CholRatioTotal CholRatioHDL     Cholesterol Ratio

aaa                              1234     1/1/12          190                    -                         //Error in calculated dimension

aaa                              1234     1/1/12          -                         50                      //Error in calculated dimension

So I end up with 190/- or -/190.  I'm trying AGGR() to see if that can collapse the CRTotal and CRHDL by the other fields in common, but I'm missing something there.

=SUM(Num(Aggr(MAX(TestDate), ClientAccountName, ExternalID), CholRatioTotal)/
(Num(Aggr(MAX(TestDate), ClientAccountName, ExternalID), CholRatioHDL)))

[vivek_niti]

Hi...

if u can do this calculation at the back end..then try this...

LOAD

      ClientAccountName,

      ExternalID,

      TestDate,

      Rangemax([CholRatio Total]) AS [CholRatio Total],

       RangeMax([CholRatio HDL]) AS [CholRatio HDL]

FROM.....

GROUP BY ClientAccountName,      ExternalID,      TestDate;

Regards,

[stevelord]

Thanks Niti.  If you are replying to the secondary issue, I also posted that separately somewhere else (which I should have done initially.)

When I tried (a very slight variation of the) script you suggested I received this error message:

Aggregation expressions required by GROUP BY clause

LOAD

  TEXT(UserId) as UserId,

  ExternalID,

  TestDate,

  Units,

  Source,

     Rangemax(HDL) AS CholRatioHDLtest,

     RangeMax([Total Cholesterol]) AS CholRatioTotaltest,

FROM Resident Biometrics

Group by UserId, TestDate, Units, Source;

Ultimately what I did as a workaround was:

export HDL and Total Cholesterol values from list boxes to their own excel worksheets

then reload HDL fields as HDL table, Total Cholesterol as Total Cholesterol table

left joined 2nd to 1st

did a Load * with if statements for my cholesterol ratio calculation, resident HDL table, drop HDL table

Boom!

Then my colleague showed me %key to make link tables to stamp over the synthetic joins that appeared.

Also did this to get Systolic / Diastolic together in one field.

I shall continue my quest for script to do this without exporting from listboxes and reloading and leftjoining rigamarole, but I have a workaround now.

(PS> Fields were created from values in another field on a QVD file of millions of last year's records way too big for transformation step or transpose etcetera.)

[vivek_niti]

Hi,

In the group by function you have to add all the fields in the group by clause which are not used in the aggregate function. you are missing ExternalID field. Just add it in the group by function and it should work. There should'nt be any need of exporting the data into an excel and then adding them in the script.

Regards,

Vivek

[stevelord]

Still resisting me I’m afraid. The overall script is long and I may be missing something that needs to be added or commented out for this piece of script to work.

I will post back here if I figure it out or get this solution to work on a simpler script. ☺

[vivek_niti]

If possible can u send the file..

[stevelord]

I can’t give the whole file because it is too big and has specific data I can’t share among the results.

I have attached a notepad file with the background script used to assemble the data, and a png file with the table layout. No worries if no solution though, I have the workaround and may figure it out from the clues so far when I am more proficient in QV.

[stevelord]

PS> Qlikview did an odd job on the image export of the table layout. On my screen the tables each only appear once, but on the export image I see a few of the biometric tables reappeared unlinked under the office/user tables to the right.

From: Lord, Steve

Sent: Thursday, May 02, 2013 3:10 PM

To: 'jive-471286403-2hop-2-7bmm@community.qlik.com'

Subject: RE: - Re: Need help pulling number values from 2 fields to use in new field in script

I can’t give the whole file because it is too big and has specific data I can’t share among the results.

I have attached a notepad file with the background script used to assemble the data, and a png file with the table layout. No worries if no solution though, I have the workaround and may figure it out from the clues so far when I am more proficient in QV.