topic Re: Manipulate binary load table in QlikView

Manipulate binary load table

Anonymous — Fri, 02 Dec 2016 05:34:39 GMT

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?

Re: Manipulate binary load table

tresB — Fri, 02 Dec 2016 05:48:38 GMT

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;

Re: Manipulate binary load table

sushil353 — Fri, 02 Dec 2016 05:50:56 GMT

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]

Re: Manipulate binary load table

Anonymous — Fri, 02 Dec 2016 05:57:42 GMT

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

Re: Manipulate binary load table

tresB — Fri, 02 Dec 2016 06:09:56 GMT

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.