How to use string content of field in SetAnalysis function?

[hugmarcel]

Hi

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

[hugmarcel]

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

[stevedark]

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

[hugmarcel]

Hi

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.

[stevedark]

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

[sunilkumarqv]

Sorry for missing file  check it out now

[hugmarcel]

wrong file uploaded? cant see a solution

[sunilkumarqv]

R u checked that