topic Re: how to remove lines form right keep in QlikView

how to remove lines form right keep

Wed, 25 Mar 2015 12:34:22 GMT

this is my code

cust:

LOAD [Customer ID],

Country,

Customer

FROM

(biff, embedded labels, table is A$);

sales:

right keep LOAD Month,

[Salesman ID],

[Customer ID],

[Transaction ID],

Year,

Day,

[Product ID],

[Serial No],

[List Price],

Sales,

[Gross Margin]

FROM

it fetches 750 lines, i want to remove the customers who are not having any sales in the final table.

Re: how to remove lines form right keep

buzzy996 — Wed, 25 Mar 2015 16:32:41 GMT

try this,

cust:

LOAD [Customer ID],

Country,

Customer

FROM

(biff, embedded labels, table is A$);

sales:

right keep LOAD Month,

[Salesman ID],

[Customer ID],

[Transaction ID],

Year,

Day,

[Product ID],

[Serial No],

[List Price],

Sales,

[Gross Margin]

FROM

FinalTable:

Load

Month,

[Salesman ID],

[Customer ID],

[Transaction ID],

Year,

Day,

[Product ID],

[Serial No],

[List Price],

Sum(Sales) as Sales,

[Gross Margin]

Resident sales

where sales>0

group by [Customer ID];

Re: how to remove lines form right keep

Wed, 25 Mar 2015 16:47:25 GMT

Hi Praveen,

Is there a specific reason you using Right Keep in your code??

You can use the following code assuming you are considering sales only by customer:

right join

Load [Customer ID],

Sum(sales) as Sum_sales

Resident sales

group by [Customer ID];

Can you post your exact output requirement?

Regards,

Shruti Kulkarni

Re: how to remove lines form right keep

Wed, 25 Mar 2015 18:31:26 GMT

i m not using this for any specific reason but i m trying to do so bcz i m new to qv.

thank you for your code and i will try it n get back to you.

Re: how to remove lines form right keep

Wed, 25 Mar 2015 18:32:11 GMT

thank you for your code and i will try it n get back to you.

Re: how to remove lines form right keep

Wed, 25 Mar 2015 20:47:33 GMT

Cs:

LOAD CustomerID,

SalesOrderID,

ProductID,

SalesAmount,

OrderQty,

Year

FROM

(ooxml, embedded labels, table is [Fact Table])

Where ProductID<>11;

Prod:

right Keep LOAD ProductID,

ProductName

FROM

(ooxml, embedded labels, table is Product);

Just try this macha

Re: how to remove lines form right keep

Fri, 27 Mar 2015 18:07:12 GMT

hi madam how r u? i have tried the code n i got the required table having customers with sales only. thank you.

Re: how to remove lines form right keep

Fri, 27 Mar 2015 18:29:49 GMT

Hi Praveen,

Please mark correct answer, if your issue is solved.

Regards,

Shruti

Re: how to remove lines form right keep

Fri, 27 Mar 2015 20:04:08 GMT

Cs:

LOAD CustomerID,

SalesOrderID,

ProductID,

SalesAmount,

OrderQty,

Year

FROM

(ooxml, embedded labels, table is [Fact Table])

Where OrderQty>=0;

Prod:

Left Keep LOAD ProductID,

ProductName

FROM

(ooxml, embedded labels, table is Product);

Try this luchaa you will get ur answer,,