making a dimesion

[#1]

Hi,

Can i make a dimension in data load editor using below values?

PAR 7 =sum(If(ArrearsRentals>0,(CapitalArrears/ArrearsRentals),0))

PAR 14 =Sum(If(ArrearsRentals>1,(CapitalArrears/ArrearsRentals),0))

PAR 30 =Sum(If(ArrearsRentals=3,(CapitalArrears/ArrearsRentals),If(ArrearsRentals>3,(CapitalArrears/ArrearsRentals)*2,0))) PAR 60 =Sum(If(ArrearsRentals=5,(CapitalArrears/ArrearsRentals), If(ArrearsRentals=6,(CapitalArrears/ArrearsRentals)*2,                If(ArrearsRentals=7,(CapitalArrears/ArrearsRentals)*3, If(ArrearsRentals>7,(CapitalArrears/ArrearsRentals)*4,0)))))

[arvind_patil]

HI Dushan,

With the help of Inline Table you can create this as a dimension.

Thanks,

Arvind Patil

[#3]

Hi Arvind, could you please share an example?

[#4]

Please help me with this!

[tresesco]

Dear Dushan,

You perhaps have to help us to help you. Please check the post: Qlik Community Tip: How to Get Answers to Your Post

[#6]

Thanks Tresesco. Please find my previous question thread

Dimension Help

But I need to create a dimension in data load editor.

[divyanshi]

In load editor after all columns loading lines you can made dimension by

TEXT(if ArrearsRentals>0,CapitalArrears/ArrearsRentals,0)) as new_column_name

May you get by this

[tresesco]

May be like:

t1:

LOAD BranchName,

    Customer,

    ArrearsRentals,

    CapitalArrears

FROM

(ooxml, embedded labels, table is Sheet1);

t2:

Load

  'PAR7' as PAR,

  sum(If(ArrearsRentals>0,(CapitalArrears/ArrearsRentals),0)) as Value

Resident t1;

Load

  'PAR14' as PAR,

  sum(If(ArrearsRentals>1,(CapitalArrears/ArrearsRentals),0)) as Value

Resident t1;

Load

  'PAR30' as PAR,

  Sum(If(ArrearsRentals=3,(CapitalArrears/ArrearsRentals),If(ArrearsRentals>3,(CapitalArrears/ArrearsRentals)*2,0))) as Value

Resident t1;

Drop table t1;

[#9]

Dear Tresesco,

How can I make a relationship between t1 and t2?

[tresesco]

There has to be a dimension on which your calculation should happen (using group by clause) and the same dimension could be used in the table to make the association work. Ideally, this calculation should not be done at the script, because that would remove the flexibility of showing the values at different level.