topic Re: How to use string content of field in SetAnalysis function? in QlikView

How to use string content of field in SetAnalysis function?

hugmarcel — Fri, 21 Feb 2014 10:09:11 GMT

see test.qvw attached.

having data:

// Data

BOOKINGS:
LOAD * INLINE [
PRODUCT, AMOUNT_SOLD, TARGET_PRODUCTS,
Product 1, 100, '"Product 1","Product 2","Product 3"'
Product 2, 200, '"Product 2","Product 3"'
Product 3, 300, '"Product 3"'
];

//Goal: Show this Pivot:
//PRODUCT, sum(AMOUNT_SOLD), sum(AMOUNT_SOLD) for TARGET_PRODUCTS
//Product 1, 100, 600 //100 + 200 + 300
//Product 2, 200, 500 //200 + 300
//Product 3, 300, 300 //300

//What is the correct formular for column 3 "sum(AMOUNT_SOLD) for TARGET_PRODUCTS"?

thx - Marcel

Re: How to use string content of field in SetAnalysis function?

MK_QSL — Fri, 21 Feb 2014 10:41:39 GMT

Use this in SCRIPT... what is your requirements?

BOOKINGS:

LOAD

PRODUCT,

AMOUNT_SOLD,

Replace(SubField([TARGET_PRODUCTS],','),'"','') as [TARGET_PRODUCTS]

INLINE

[

PRODUCT, AMOUNT_SOLD, TARGET_PRODUCTS,

Product 1, 100, '"Product 1","Product 2","Product 3"'

Product 2, 200, '"Product 2","Product 3"'

Product 3, 300, '"Product 3"'

];

UPDATE : FILE ENCLOSED.

Re: How to use string content of field in SetAnalysis function?

hugmarcel — Fri, 21 Feb 2014 11:12:21 GMT

I do not want to have the products listed, but the amounts calculated.

So column3 of the pivot should contain values 600, 500 and 300.

Thx - Marcel

Re: How to use string content of field in SetAnalysis function?

MK_QSL — Fri, 21 Feb 2014 11:25:34 GMT

Sorry but i dont understand your requirement.

Can you clarify?

Re: How to use string content of field in SetAnalysis function?

Anonymous — Fri, 21 Feb 2014 11:27:23 GMT

Hi Marcel,

use rangesum (Below(AMOUNT_SOLD,0,3)) formula ..

I have done this Plz find the attachment..

Re: How to use string content of field in SetAnalysis function?

giakoum — Fri, 21 Feb 2014 11:39:39 GMT

See attached

Re: How to use string content of field in SetAnalysis function?

Anonymous — Fri, 21 Feb 2014 11:46:13 GMT

Hi Marcel,

Plz see the attachment. It is correct as your requirement..

Re: How to use string content of field in SetAnalysis function?

hugmarcel — Fri, 21 Feb 2014 12:42:25 GMT

rangesum (Below(AMOUNT_SOLD,0,3)) is not generally correct, as it uses the order of the products for calculation, but not the target_product string in the table.

To have a general solution, the target_product data must be part of the formula.

Thx - Marcel

Re: How to use string content of field in SetAnalysis function?

hugmarcel — Fri, 21 Feb 2014 12:45:15 GMT

the task is to solve it in the SetAnalysis Function, not to change the data model.

Thx - Marcel

Re: How to use string content of field in SetAnalysis function?

MK_QSL — Fri, 21 Feb 2014 12:49:06 GMT

can you explain below?

What is your logic to receive below ?

So column3 of the pivot should contain values 600, 500 and 300.

Re: How to use string content of field in SetAnalysis function?

hugmarcel — Fri, 21 Feb 2014 12:53:59 GMT

the logic / requirment is to evaluate the content of field TARGET_PRODUCTS in the SetAnalysis Formula.

TARGET_PRODUCTS has 3 values

"Product1","Product2","Product3"

"Product2","Product3"

"Product3"

thx

Re: Re: How to use string content of field in SetAnalysis function?

stevedark — Fri, 21 Feb 2014 13:10:41 GMT

I'm not sure of the logic behind what you are after, but I believe this script will achieve it:

Temp_Source:

LOAD

INLINE [

PRODUCT, AMOUNT_SOLD, TARGET_PRODUCTS,

Product 1, 100, '"Product 1","Product 2","Product 3"'

Product 2, 200, '"Product 2","Product 3"'

Product 3, 300, '"Product 3"'

];

BOOKINGS:

LOAD

PRODUCT,

Replace(SubField(TARGET_PRODUCTS, ','), '"', '') as JOINPRODUCT

RESIDENT Temp_Source

;

LEFT JOIN (BOOKINGS)

LOAD

PRODUCT as JOINPRODUCT,

AMOUNT_SOLD

RESIDENT Temp_Source;

DROP TABLE Temp_Source;

Solution attached also.

Steve Dark

Re: How to use string content of field in SetAnalysis function?

hugmarcel — Fri, 21 Feb 2014 13:24:35 GMT

thx for the answer, as said, I do not want to change the data model.

For Product 1, TARGET_PRODUCTS amount should be 600, because

TARGET_PRODUCTS field contains string "Product 1","Product 2","Product 3", indicating that individual

amounts 300 (Product 1), 200 (Product 2), 100 (Product 1) must be summed.

For Product 2, TARGET_PRODUCTS amount should be 500, because

TARGET_PRODUCTS field contains string "Product 2","Product 3", indicating that individual

amounts 300 (Product 1), 200 (Product 2) must be summed.

Calculation should be done in SetAnalysis function, thus evaluating content of TARGET_PRODUCTS in the formula.

etc.

Re: How to use string content of field in SetAnalysis function?

stevedark — Fri, 21 Feb 2014 13:51:12 GMT

Hi Marcel,

You will struggle to do this in Set Analysis. As soon as you do a dollar expansion ($()) this will be evaluated at a global rather than a dimensional level.

I have tried with index and match functions and with Aggr, but I don't think you will get there.

This alternate script leaves the original data model intact, but gives you another value (TARGET_AMOUNT_SOLD) you can tot up and get the value you are after:

Temp_Source:

LOAD

INLINE [

PRODUCT, AMOUNT_SOLD, TARGET_PRODUCTS,

Product 1, 100, '"Product 1","Product 2","Product 3"'

Product 2, 200, '"Product 2","Product 3"'

Product 3, 300, '"Product 3"'

];

BOOKINGS:

NOCONCATENATE LOAD

PRODUCT,

AMOUNT_SOLD,

TARGET_PRODUCTS

RESIDENT Temp_Source

;

AllAmounts:

LOAD

Replace(SubField(TARGET_PRODUCTS, ','), '"', '') as PRODUCT,

AMOUNT_SOLD as TARGET_AMOUNT_SOLD

RESIDENT Temp_Source;

DROP TABLE Temp_Source;

Steve Dark

Re: How to use string content of field in SetAnalysis function?

sunilkumarqv — Fri, 21 Feb 2014 13:51:30 GMT

Sorry for missing file check it out now

Re: How to use string content of field in SetAnalysis function?

hugmarcel — Fri, 21 Feb 2014 13:55:08 GMT

wrong file uploaded? cant see a solution

Re: How to use string content of field in SetAnalysis function?

sunilkumarqv — Fri, 21 Feb 2014 13:59:10 GMT

R u checked that