Need to have a formula to calculate the result based upon 2 different cell/condition

Singh

New member
Joined
Dec 12, 2015
Messages
3
Reaction score
0
Points
0
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 Type Shipment Type
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

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.
 
Dear Herbds7,

Thank you for your help, but unfortunately this is of no use and is way far from my requirement. This is possible as people have sent me replies on qlik. following are few of them:


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
] ;


  • ___________________---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    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';
    See if this can help you as i could not understand how to use these codes and the file given as attachment is in .qvw format which is not opening in my system.
 
you could try
=IF(COUNTIFS($A$2:$A$23,A2,$B$2:$B$23,"Cold Chain")>0,"Cold Chain","something else")
in C2 and copy down.
 
you could try
=IF(COUNTIFS($A$2:$A$23,A2,$B$2:$B$23,"Cold Chain")>0,"Cold Chain","something else")
in C2 and copy down.


Hi p45cal,

Thank you for your help. but this is not solving the problem. I will elaborate my objective:

If i have a invoice having material of Cold & Non cold nature, the shipment should automatically come as Cold Shipment as the shipment will be treated as Cold only. For example:

Invoice No. Product Description Product Type Shipment Type
A/1 ABC Normal
A/1 DEF Normal
A/1 GHI Cold

Now in this case, the shipment type should be Cold and if all the product type are normal, then Normal.

It would be a great help if you can provide me with this formula.

Thank you in advance.

Singh
 
Please find that attachment.

In cell C2 type the formula as =IF(COUNTIFS($A$2:A2,A2,$B$2:B2,"Cold Chain")>0,"Cold Chain","") and copy the same in rows as you desired.

Hope you are asking this type of solution.
 

Attachments

  • Countifs.xlsx
    9.8 KB · Views: 8
Back
Top