topic Re: making a dimesion in QlikView

making a dimesion

Thu, 13 Jul 2017 06:09:27 GMT

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)))))

Re: making a dimesion

arvind_patil — Thu, 13 Jul 2017 06:17:58 GMT

HI Dushan,

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

Thanks,

Arvind Patil

Re: making a dimesion

Thu, 13 Jul 2017 06:38:59 GMT

Hi Arvind, could you please share an example?

Re: making a dimesion

Fri, 14 Jul 2017 09:50:16 GMT

Please help me with this!

Re: making a dimesion

tresB — Fri, 14 Jul 2017 10:23:42 GMT

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

Re: making a dimesion

Fri, 14 Jul 2017 11:11:45 GMT

Thanks Tresesco. Please find my previous question thread

Dimension Help

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

Re: making a dimesion

divyanshi — Fri, 14 Jul 2017 11:19:42 GMT

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

Re: making a dimesion

tresB — Fri, 14 Jul 2017 11:32:35 GMT

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;

Re: making a dimesion

Mon, 17 Jul 2017 09:44:42 GMT

Dear Tresesco,

How can I make a relationship between t1 and t2?

Re: making a dimesion

tresB — Mon, 17 Jul 2017 09:53:36 GMT

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.

Re: making a dimesion

Tue, 18 Jul 2017 04:23:27 GMT

Hi Tresesco,

Could you please send me an example in qliksense. Thanks