Error comes in data loading with epresion written just under a calculated age

[nevilledhamsiri]

Hi,

I require to arrange a an age range for Debtors. The TRN Date & the AS AT DATE were loaded as dimension & the age as calculated through VDate through 'variable overview'. (vdate-Trn date) which is in the script (data loaded attached). After that the expression was written for age buckets just after that. But an error comes always when the data are loaded. Is it because the age being  a calculated expression?. I Need to calculate ages  as I have done & similarly to create the buckets which should be able to be created as a list box. Will you explain how this could be achieved meeting my dual objectives?. The script & the pivot table attached for your easy reference.

Note: My objective was achieved if I arranged the age in the excel itself before data was loaded. But my requirement is to

(1) Get the age through an expression as I have done

(2) To be able to create the bucket as a list box.

DBTORS:

LOAD TRN_DATE,

     AS_AT_DATE,

    AS_AT_DATE-TRN_DATE AS AGE, -----------------(This is how I calculate ages for Debtors)

        IF((AGE>0)AND(AGE<=30),'0-30',

     IF((AGE>30)AND(AGE<=60),'30-60',

     IF((AGE>60)AND(AGE<=90),'60-90','>90'))) AS AGE_BUCKET,

     DEBTOR,

     [POLICY NUMBER],

     [AMOUNT O/S]

FROM

[..\AGE(TEST).xlsx]

(ooxml, embedded labels, table is [Sheet1 (2)]);

[vinieme12 · Accepted solution]

You just gave an alias to a field and you want to refer it in the same load statement!!

That is never happening, you have to use the original field name because your data is still loading and aliases are assigned after the end of the load; so to Qlikview the field Age doesn't exist while loading

You can however use a Preceding load or do the same calculation to create the Age field in the same load statement.

does that make sense?

////////////////////////////////////////

Using Preceding load:

////////////////////////////////////////

DBTORS:

LOAD * ,

        IF((AGE>0)AND(AGE<=30),'0-30',

     IF((AGE>30)AND(AGE<=60),'30-60',

     IF((AGE>60)AND(AGE<=90),'60-90','>90'))) AS AGE_BUCKET ;

LOAD TRN_DATE,

     AS_AT_DATE,

    AS_AT_DATE-TRN_DATE AS AGE,

     DEBTOR,

     [POLICY NUMBER],

     [AMOUNT O/S]

FROM

[..\AGE(TEST).xlsx]

(ooxml, embedded labels, table is [Sheet1 (2)]);

////////////////////////////////////////

Without  Preceding load:

////////////////////////////////////////

DBTORS:

LOAD TRN_DATE,

     AS_AT_DATE,

    (AS_AT_DATE-TRN_DATE) AS AGE,

     if( (AS_AT_DATE-TRN_DATE) > 90 , '>90',

               if( (AS_AT_DATE-TRN_DATE) > 60 , '60-90',         

                      if( (AS_AT_DATE-TRN_DATE) > 30 , '30-60', 

                           if( (AS_AT_DATE-TRN_DATE) > 30 , '30-60',  '0-30' ) ) ) )  as AGE_BUCKET,

     DEBTOR,

     [POLICY NUMBER],

     [AMOUNT O/S]

FROM

[..\AGE(TEST).xlsx]

(ooxml, embedded labels, table is [Sheet1 (2)]);

[vinieme12]

If you've found your answer then, Please close the thread by marking the response as correct

Qlik Community Tip: Marking Replies as Correct or Helpful

[nevilledhamsiri]

Dear Mr Puneeth,

Thank for your solution!. It worked. Will you explain me following two points as well if possible. I will of course close this tread & others immediately. Sorry for the inconvience caused. My two issues are!

(1) Once age bucket is achieved & once it was created as a list box, it is not in an order such as ascending order. I used the load order & changed the order their but it did not work.

(2) For the age, I used vdate which is 08/31/2017 through variable overview & how this date could be used in the script which is not in the script like as at date to calculate age bucket.

[vinieme12]

(1) Once age bucket is achieved & once it was created as a list box, it is not in an order such as ascending order. I used the load order & changed the order their but it did not work.

     --Use Dual() to assign a numeric value to your text or create a Mapping table associated to Age_Bucket field that           specifies the SortOrder and then in chart's use Sort by Expression enter =AB_SortOrder:

     Example

  LOAD * INLINE [

AGE_BUCKET,AB_SortOrder

>90,1

60-90,2

and so on

];

(2) For the age, I used vdate which is 08/31/2017 through variable overview & how this date could be used in the script which is not in the script like as at date to calculate age bucket.

You can use variables in script using LET statement

example

LET vdate  = DATE#(08/31/2017,'MM/DD/YYYY');

and refer in load statements with  $() expansion  

example

     LOAD  $(vdate) as DATEIS;

[vinieme12]

Refer these threads

Dual ‒ QlikView

How to use- Dual()

QlikView Addict: SET vs. LET

[nevilledhamsiri]

Dear Mr Vineeth,

Below exprsession is something you had written for me when calculation age as the difference between two dates (as at date-TRN Date) that is only when as at date is there as a dimension. But when the as at date is not there as a dimension I sought your opinion as to how I could present it in the script. The answer given by you is by using let statement. But I tried it in the load statement as you mentioned but all the fields were disappeared. May be the place & the way I write it in the script must be  wrong. Appreciate, if you properly laid down this.If you use the bucket you made below using the let statement date as your early reply that would be fine. Also you  mentioned  like below!

let vdate=date#(08/31/2017,'MM/DD/YYYY');

Load $ (vdate) as daties

Please use the above in the below data loading clearly so that I can understand well. Sorry for troubling you. Specially I have no idea where I should write the above on the top of the below data loading, in the middle or at the bottom.

What I need is to be able to calculate the age in the script (08/31/2017-TRN_Date) hereby to be able to create age bucket in the script. You kept guiding me towards this. If possible please show it as a complete data loading so that I will be able to understand better!

DBTORS:

LOAD TRN_DATE,

     AS_AT_DATE,

    (AS_AT_DATE-TRN_DATE) AS AGE,

     if( (AS_AT_DATE-TRN_DATE) > 90 , '>90',

               if( (AS_AT_DATE-TRN_DATE) > 60 , '60-90',         

                      if( (AS_AT_DATE-TRN_DATE) > 30 , '30-60', 

                           if( (AS_AT_DATE-TRN_DATE) > 30 , '30-60',  '0-30' ) ) ) )  as AGE_BUCKET,

     DEBTOR,

     [POLICY NUMBER],

     [AMOUNT O/S]

FROM

[..\AGE(TEST).xlsx]

(ooxml, embedded labels, table is [Sheet1 (2)]);