topic Re: amount colum require with condisions in QlikView

amount colum require with condisions

yadav_anil782 — Mon, 15 Jun 2015 07:36:38 GMT

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

Re: amount colum require with condisions

Mon, 15 Jun 2015 08:08:33 GMT

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?

Re: amount colum require with condisions

Mon, 15 Jun 2015 08:13:37 GMT

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,

Re: amount colum require with condisions

fvelascog72 — Mon, 15 Jun 2015 08:20:12 GMT

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

Re: amount colum require with condisions

Mon, 15 Jun 2015 08:21:43 GMT

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

settu_periasamy — Mon, 15 Jun 2015 08:25:57 GMT

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

Re: amount colum require with condisions

Mon, 15 Jun 2015 08:28:42 GMT

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)

Re: amount colum require with condisions

yadav_anil782 — Mon, 15 Jun 2015 08:35:57 GMT

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

Re: amount colum require with condisions

Mon, 15 Jun 2015 08:54:57 GMT

Hi Anil Kumar,

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

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

Regards,

Srinath.

Re: amount colum require with condisions

sasiparupudi1 — Mon, 15 Jun 2015 09:41:07 GMT

Please check the attachment

@ !