Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

How to use string content of field in SetAnalysis function?

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

16 Replies
Highlighted
MVP
MVP

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.

Highlighted
Creator III
Creator III

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

Highlighted
MVP
MVP

Sorry but i dont understand your requirement.

Can you clarify?

Highlighted
Creator
Creator

Hi Marcel,

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

I have done this Plz find the attachment..

Highlighted
Partner
Partner

See attached

Highlighted
Creator
Creator

Hi Marcel,

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

Highlighted
Creator III
Creator III

Hi

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

Highlighted
Creator III
Creator III

Hi

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

Thx - Marcel

Highlighted
MVP
MVP

can you explain below?

What is your logic  to receive below ?

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