Manipulate binary load table

[Anonymous]

Hi guys , i am creating a qlikview script to add additional column for data categorization to a existing table from qvw file.

heres the script:

Binary C:\Users\tan\Desktop\Data Model\DATA_MODEL.qvw;

Rename table FACTS to FACTS_temp;

[FACTS]:

load *,

if(IsText(mid(fin_accnt_code,8,1)) and fin_gl_line_allocation=' ',fin_gl_line_due_datetime - today()) as [Aged Days],

if([Aged Days]<0,'Current',

     if([Aged Days]<=30,'1-30',

          if([Aged Days]<=60,'31-60',

          if([Aged Days]<=90,'61-90',

          if([Aged Days]<=180,'91-180',

          if([Aged Days]<=365,'181-365','365+'))))))

            as Category

Resident [FACTS_temp];

drop table facts_temp;

But the above script does not work as Aged Days is not created yet, is there any other way to do this?

[tresesco · Accepted solution]

Try with preceding load like:

Binary C:\Users\tan\Desktop\Data Model\DATA_MODEL.qvw;

Rename table FACTS to FACTS_temp;

[FACTS]:

load *,

if([Aged Days]<0,'Current',

     if([Aged Days]<=30,'1-30',

          if([Aged Days]<=60,'31-60',

          if([Aged Days]<=90,'61-90',

          if([Aged Days]<=180,'91-180',

          if([Aged Days]<=365,'181-365','365+'))))))

            as Category ;

load *,

if(IsText(mid(fin_accnt_code,8,1)) and fin_gl_line_allocation=' ',fin_gl_line_due_datetime - today()) as [Aged Days]

Resident [FACTS_temp];

drop table facts_temp;

[sushil353]

Try this:

Binary C:\Users\tan\Desktop\Data Model\DATA_MODEL.qvw;

Rename table FACTS to FACTS_temp;

[FACTS]:

load *,

if([Aged Days]<0,'Current',

     if([Aged Days]<=30,'1-30',

          if([Aged Days]<=60,'31-60',

          if([Aged Days]<=90,'61-90',

          if([Aged Days]<=180,'91-180',

          if([Aged Days]<=365,'181-365','365+'))))))

            as Category;

load *,

if(IsText(mid(fin_accnt_code,8,1)) and fin_gl_line_allocation=' ',fin_gl_line_due_datetime - today()) as [Aged Days],

Resident [FACTS_temp]

[Anonymous]

that works, thanks guys but is there any other way to prevent loading the whole table twice? its a really huge table

[tresesco]

One option is to create the age bucket in the DATA_MODEL.qvw itself. Otherwise, one resident load would be required and that can't be avoided.