If statements

Fri, 21 Jun 2013 15:12:08 GMT

Hi everyone,

I am a finance and IT intern, and am new to Qlikview. I have no experience in coding b/c I only finished my freshman year of college in May. One of my projects is to use Qlikview to display data regarding our Accounts Receivable and AR days. I work for an insurance provider and we have to track dozens of payors and the money they owe us, and how long they have owed us a certain amount of money. Right now, I am pulling in the raw data from a database.

There are currently the following time buckets for accounts receivable:

0-30 days

31-60 days

61-90 days

91-120 days

121-150 days

151-180 days

181 & up days

We also have raw data that shows the amount of 'days' each AR has been active, and I want to group them as specified above.

Each 'time bucket' is listed as a separate dimension, and I want to combine them all into one dimension titled "Time_Bucket". That way, when I want to make graphs and charts, I can select "Time_Bucket", rather than all the separate time buckets.

The following is what I have developed so far, but I must be doing something incorrectly b/c there are errors reloading the data:

if( line_item_age >=0 and

<=30, '0-30',
if( line_item_age >=30 and <=60, '0-60',
if( line_item_age >=61 and <=90, '61-90',
if( line_item_age >=91 and <=120, '91-120',
if( line_item_age >=121 and <=150, '121-150'
if( line_item_age >=151 and <=180, '151-180'
if( line_item_age >=181, '181 up'))))))), 'Time_Bucket',

(below is the error message I receive):

Error in expression:
')' expected
LOAD "practice_id",
    "enc_id",
    "charge_id",
    "person_id",
    "charge_amt",
    quantity,
    "line_item_amt",
    "insured_person_id",
    "payer_id",
    "payer_name",
    "financial_class",
    "financial_class_desc",
    if( line_item_age >=0 and <=30, '0-30',
if( line_item_age >=30 and <=60, '0-60',
if( line_item_age >=61 and <=90, '61-90',
if( line_item_age >=91 and <=120, '91-120',
if( line_item_age >=121 and <=150, '121-150'
if( line_item_age >=151 and <=180, '151-180'
if( line_item_age >=181, '181 up'))))))), 'Time_Bucket',

    "charge_status",
    "charge_status_desc",
    "begin_date_of_service",
    "service_item_lib_id",
    "service_item_id",
    "source_type",
    date(date#("closing_date",'YYYYMMDD'),'M/D/YYYY') as closing_date,
    "guar_id",
    "billable_timestamp",
    "line_item_age",
    "create_timestamp",
    "unit_price",
    cob,
    "cob1_amt",
    "cob2_amt",
    "cob3_amt",
    "pat_amt",
    "exception_amt",
    "chg_amt_0_30",
    "chg_amt_31_60",
    "chg_amt_61_90",
    "chg_amt_91_120",
    "chg_amt_121_150",
    "chg_amt_151_180",
    "chg_amt_181_up",
    "unapplied_trans_y_n",
    "total_paid",
    "total_adj",
    "unapplied_ind",
    "unapplied_amt",
    "person_payer_id",
    "enc_rendering_id",
    "enc_rendering_name",
    "enc_nbr",
    "baddebt_amt",
    "self_less_baddebt_amt"

**If someone could please help me figure how to do this, or at least guide me in the right direction, that would be much appreciated, thanks!

Paul

Re: If statements

swuehl — Fri, 21 Jun 2013 15:28:21 GMT

Try something like

if( line_item_age >=0 and
line_item_age <=30, '0-30',
if( line_item_age <=60, '31-60',
if( line_item_age <=90, '61-90',
if( line_item_age <=120, '91-120',
if( line_item_age <=150, '121-150'
if( line_item_age <=180, '151-180'
,'181 up' )))))) as [Time_Bucket],

Re: If statements

Tue, 25 Jun 2013 13:10:34 GMT

Thank you that worked well!

topic If statements in QlikView

If statements

Re: If statements

Re: If statements