banding - is fractile the asnwer?

[#1]

Hi,

On the attached you will see we have a list of repairs total values. Want we want to do is band/ rank them to help us start segmenting. So for example  address 1 has had £38 of repairs but be great to know in the context of this sample (248 addresses)  which band this sits in, e.g band 6. Note we don't just want to band the addresses from 1 to 238 - we want to band into top groups (i.e top 10% to bottom 10%).

We tried to do this using fractile and whilst the script has no errors it didn't do anything:

LOAD "tency_seq_no" as [Tenancy No], "yr_period", "Tenant_Arrears",

  if("Tenant_Arrears" <= fractile(TOTAL "Tenant_Arrears", 0.1), 1,

if("Tenant_Arrears" <= fractile(TOTAL "Tenant_Arrears", 0.2), 2,

if("Tenant_Arrears" <= fractile(TOTAL "Tenant_Arrears", 0.3), 3,

if("Tenant_Arrears" <= fractile(TOTAL "Tenant_Arrears", 0.4), 4,

if("Tenant_Arrears" <= fractile(TOTAL "Tenant_Arrears", 0.5), 5,

if("Tenant_Arrears" <= fractile(TOTAL "Tenant_Arrears", 0.6), 6,

if("Tenant_Arrears" <= fractile(TOTAL "Tenant_Arrears", 0.7), 7,

if("Tenant_Arrears" <= fractile(TOTAL "Tenant_Arrears", 0.8), 8,

if("Tenant_Arrears" <= fractile(TOTAL "Tenant_Arrears", 0.9), 9,

if("Tenant_Arrears" <= fractile(TOTAL "Tenant_Arrears", 1.0),10))))))))))

Chris

[Chanty4u]

hi,

did u tried  remvng the double quotes

if(Tenant_Arrears <= fractile(TOTAL "Tenant_Arrears", 0.1), 1,

[sunny_talwar]

Something like this:

Table:

LOAD Address,

    [Repairs total value]

FROM

[qlikview fractile.xls]

(biff, embedded labels, table is Sheet1$);

Join (Table)

LOAD Fractile([Repairs total value], 0.1) as Fractile1,

  Fractile([Repairs total value], 0.2) as Fractile2,

  Fractile([Repairs total value], 0.3) as Fractile3,

  Fractile([Repairs total value], 0.4) as Fractile4,

  Fractile([Repairs total value], 0.5) as Fractile5,

  Fractile([Repairs total value], 0.6) as Fractile6,

  Fractile([Repairs total value], 0.7) as Fractile7,

  Fractile([Repairs total value], 0.8) as Fractile8,

  Fractile([Repairs total value], 0.9) as Fractile9,

  Fractile([Repairs total value], 0.10) as Fractile10

Resident Table;

FinalTable:

LOAD Address,

  [Repairs total value],

  if([Repairs total value] <= Fractile1, 1,

  if([Repairs total value] <= Fractile2, 2,

  if([Repairs total value] <= Fractile3, 3,

  if([Repairs total value] <= Fractile4, 4,

  if([Repairs total value] <= Fractile5, 5,

  if([Repairs total value] <= Fractile6, 6,

  if([Repairs total value] <= Fractile7, 7,

  if([Repairs total value] <= Fractile8, 8,

  if([Repairs total value] <= Fractile9, 9,

  if([Repairs total value] <= Fractile10,10, 11)))))))))) as RequiredCol

Resident Table;

DROP Table Table;

[Anonymous]

Sunny,

How this joining is happening here as their is no common field?

Table:

LOAD Address,

    [Repairs total value]

FROM

[qlikview fractile.xls]

(biff, embedded labels, table is Sheet1$);

Join (Table)

LOAD Fractile([Repairs total value], 0.1) as Fractile1,

  Fractile([Repairs total value], 0.2) as Fractile2,

  Fractile([Repairs total value], 0.3) as Fractile3,

  Fractile([Repairs total value], 0.4) as Fractile4,

  Fractile([Repairs total value], 0.5) as Fractile5,

  Fractile([Repairs total value], 0.6) as Fractile6,

  Fractile([Repairs total value], 0.7) as Fractile7,

  Fractile([Repairs total value], 0.8) as Fractile8,

  Fractile([Repairs total value], 0.9) as Fractile9,

  Fractile([Repairs total value], 0.10) as Fractile10

Resident Table;

Is it taking it as a outer join without any common field?

[Kushal_Chawda]

It's basically a cross join. In this case join will happen for all possible combination field value. If the amount of data is huge it should be avoided

[Anonymous]

Okay, you mean to say, for cross join we dont required any common link/Field, Is It?

[sunny_talwar]

This particular cross join is not as bad as you may think Kush141087‌, Balraj Ahlawat‌. I am just joining a single row to multiple rows. Single Row because Fractile(Field, 0.1) will be aggregated across all the fields and will give a single row output. So I don't think join is any issue. Issue is Aggregation itself which can take hours based on the size of the data and system resources.

Best,

Sunny

[#8]

Hi Sunny,

Thanks for this.

We wouldn't need the load and resident table as in the real version we want to incorporate this within data coming from our housing management system. Here is section of the script - you will see total value below (this is repairs total value you did above):

LOAD //[Place Ref],

     [Tenancy Ref]as [Tenancy No],

    // [START-DATE""],

     [Job No],

     [Date Reported],

     [Date Comp],

     [Job Type],

     Contract,

     [Trade Code],

     [Total Value],

     [Job Status Map],

     [Recharge Tenant]

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Is this possible and how would it look?

[sunny_talwar]

You will need to calculate the fractile and join it back your your current load and then perform your if statement:

Table:

LOAD //[Place Ref],

    [Tenancy Ref]as [Tenancy No],

    // [START-DATE""],

    [Job No],

    [Date Reported],

    [Date Comp],

    [Job Type],

    Contract,

    [Trade Code],

    [Total Value],

    [Job Status Map],

    [Recharge Tenant]

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Join (Table)

LOAD Fractile([Total Value], 0.1) as Fractile1,

  Fractile([Total Value], 0.2) as Fractile2,

  Fractile([Total Value], 0.3) as Fractile3,

  Fractile([Total Value], 0.4) as Fractile4,

  Fractile([Total Value], 0.5) as Fractile5,

  Fractile([Total Value], 0.6) as Fractile6,

  Fractile([Total Value], 0.7) as Fractile7,

  Fractile([Total Value], 0.8) as Fractile8,

  Fractile([Total Value], 0.9) as Fractile9,

  Fractile([Total Value], 0.10) as Fractile10

Resident Table;

FinalTable:

LOAD *,

  if([Total Value] <= Fractile1, 1,

  if([Total Value] <= Fractile2, 2,

  if([Total Value] <= Fractile3, 3,

  if([Total Value] <= Fractile4, 4,

  if([Total Value] <= Fractile5, 5,

  if([Total Value] <= Fractile6, 6,

  if([Total Value] <= Fractile7, 7,

  if([Total Value] <= Fractile8, 8,

  if([Total Value] <= Fractile9, 9,

  if([Total Value] <= Fractile10,10, 11)))))))))) as RequiredCol

Resident Table;

DROP Table Table;

[#10]

HI Sunny,

So to clarify we would load the final table in the script? and the join table is done separately outside the app/document?

There is no way of doing this within the existing script?

Chris