Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
yadav_anil782
Contributor

amount colum require with condisions

Dear All,

i'm stuck in  creating amount coumn with codision ,

i have attached excel sheet in which two cloumn "close_code and "po_status" , there are status in both cloumn and i need only status from close_code("closed to receiving ","open" )and from  po_status("approved") and finely with those criteria now i need to multipulate

"QUANTITY""UNIT_PRICE"

column and final out put will be my required value. this is very tuff to me , plz help if any one can do this  and revert asap

9 Replies
Not applicable

Re: amount colum require with condisions

I am not quite sure exactly what you want, do you only want to show the rows with those statuses. In any case I would go for using set analysis. Have you tried set analysis?

Not applicable

Re: amount colum require with condisions

Hi,

While loading the data, add additional column as given below

LOAD

    Quantity,

     Price,

     Quantity*Price as Total

FROM

Your Path

You will get the desired result.

Regards,

KC

fvelascog72
Valued Contributor

Re: amount colum require with condisions

Hi,

Try this:

DATA:

  LOAD APPROVED_DATE,

     CATEGORY_DESCRIPTION,

     CLOSED_CODE,

     ITEM_CODE,

     ITEM_DESCRIPTION,

     LINE_NUM,

     PO_CREATION_DATE,

     PO_DISTRIBUTION_ID,

     PO_NO,

     PO_STATUS,

     QUANTITY,

     UNIT_PRICE,

     [require value(quantity* unit_price)],

     UOM,

     VENDOR_ID,

     VENDOR_NAME,

     VENDOR_SITE_CODE,

     VENDOR_SITE_ID,

     QUANTITY * UNIT_PRICE AS "PRICE"

FROM

[column value.xlsx]

(ooxml, embedded labels, table is Sheet1)

Where CLOSED_CODE = 'CLOSED FOR RECEIVING' OR CLOSED_CODE = 'OPEN' OR PO_STATUS = 'APPROVED';

Not applicable

Re: amount colum require with condisions

If I understand correctly he only wants to see the Quantity*Price where PO_STATUS = Approved and CLOSED_CODE = 'OPEN' and CLOSED FOR RECEIVING

Re: amount colum require with condisions

Hi,

May be try this..

Directory;

LOAD *,

     if((CLOSED_CODE='CLOSED FOR RECEIVING' or CLOSED_CODE='OPEN') and PO_STATUS='Approved',QUANTITY*UNIT_PRICE) as QTY_UNITPRICE

    

FROM

[column value.xlsx]

(ooxml, embedded labels, table is Sheet1);

Not applicable

Re: amount colum require with condisions

If you only need the values for those statuses but still need to bring the rest of the rows you can add something like this

if( PO_STATUS = 'Approved' and (CLOSED_CODE = 'OPEN' or CLOSED_CODE = 'CLOSED FOR RECEIVING',Quantity*Price)

yadav_anil782
Contributor

Re: amount colum require with condisions

Hi,

i wants to genearte one new column with these condision

i'm again explaining what i need -

1- need to check status ("closed to receiving ","open") in close_code column

2- aftter ten need to check "approved" satus in po_status

3- according to these filters now need to calculate "quantity * unit_price =  require column

plz help for the same

Not applicable

Re: amount colum require with condisions

Hi Anil Kumar,

Use a straight table or any other visualization and give the dimensions whatever you want and write the expression as:

test1.PNGtest2.PNG

Then you will get the answer you need according to your conditions.

Regards,

Srinath.

sasiparupudi1
Honored Contributor III

Re: amount colum require with condisions

Hi

Please check the attachment

PO.png@ !

Community Browser