Qlik Community

Qlik DataMarket Discussions

Discussion Board for collaboration regarding Qlik DataMarket.

Not applicable

Need to have a formula to calculate the result based upon two different cell and conditions

Dear All,

I have a sheet as below:

  

Invoice No.Product TypeShipment Type
A1Cold Chain
A1Non Cold
A1Cold Chain
A1Non Cold
A1Non Cold
B1Cold Chain
B1Cold Chain
B1Cold Chain
B1Cold Chain
B1Cold Chain
C2Non Cold
C2Non Cold
C2Non Cold
C2Non Cold
C2Non Cold
C2Non Cold
C2Non Cold
D2Non Cold
D2Non Cold
D2Non Cold
D2Non Cold
D2Cold Chain

In the table above, for any invoice, if it contains even one "Cold Chain" product type, the shipment type should be "Cold Chain" for all entries of that invoice.

For Example: I have 5 items in invoice A1, of which 2 are cold chain and 3 are non cold. Need a formula to ensure, against each entry of invoice no. A1, it should define Shipment type as Cold Chain.

Please help me to get the solution of. You can also mark me on my personal mail id - saurabh_kumar09@yahoo.com

5 Replies
MVP
MVP

Re: Need to have a formula to calculate the result based upon two different cell and conditions

see tha attchment

1.png

MVP
MVP

Re: Need to have a formula to calculate the result based upon two different cell and conditions

Try this:

Table:

LOAD *,

  RowNo() as Key

Inline [

Invoice No., Product Type

A1, Non Cold

A1, Cold Chain

A1, Non Cold

A1, Non Cold

B1, Cold Chain

B1, Cold Chain

B1, Cold Chain

B1, Cold Chain

B1, Cold Chain

C2, Non Cold

C2, Non Cold

C2, Non Cold

C2, Non Cold

C2, Non Cold

C2, Non Cold

C2, Non Cold

D2, Non Cold

D2, Non Cold

D2, Non Cold

D2, Non Cold

D2, Cold Chain

];

Left Join (Table)

LOAD Distinct [Invoice No.],

  [Product Type] as [Shipment Type]

Resident Table

Where [Product Type] = 'Cold Chain';


Capture.PNG

Not applicable

Re: Need to have a formula to calculate the result based upon two different cell and conditions

Thank you very much for your prompt response.

I request you to upload a excel file as i am unable to open the .qvw file extension.

Cheers

Not applicable

Re: Need to have a formula to calculate the result based upon two different cell and conditions

Thank you for your response. This is to be added as Macro i suppose, i mean adding a module and add this code there. Correct me if i am wrong.

Also, it would be great to have a formula directly as i need to have this used at many different locations and this is making it complicated as i also need to train my guys down the line for these formulas that i use in my sheet to ensure correctness of data. however, i have the option of adding protection and i have done that to save the formulated columns from being changed.Also, whatever you are attaching, please attach in excel format as .qvw format is not recognised by my system and i have no option to open this extension file.

Thank you

cheers

MVP
MVP

Re: Need to have a formula to calculate the result based upon two different cell and conditions

CHART

straight table chart with

dimensions:   

    InvoiceNo

    id

expressions:

    ProductType

    if(count({$ <ProductType={'Cold Chain'}>} TOTAL <InvoiceNo> ProductType) >0, 'Cold Chain', ProductType)


In presentation tab, Hide Column for id dimension

SCRIPT

I just added the id field

x:

load rowno() as id, * inline [

InvoiceNo, ProductType

A1, Cold Chain

A1, Non Cold

A1, Cold Chain

A1, Non Cold

A1, Non Cold

B1, Cold Chain

B1, Cold Chain

B1, Cold Chain

B1, Cold Chain

B1 ,Cold Chain

C2, Non Cold

C2, Non Cold

C2, Non Cold

C2, Non Cold

C2, Non Cold

C2, Non Cold

C2, Non Cold

D2, Non Cold

D2, Non Cold

D2, Non Cold

D2, Non Cold

D2, Cold Chain

] ;

Community Browser