Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

how to remove lines form right keep

hi

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.

Tags (1)
8 Replies
buzzy996
Honored Contributor II

Re: how to remove lines form right keep

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

Not applicable

Re: how to remove lines form right keep

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

Not applicable

Re: how to remove lines form right keep

hi

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.

Not applicable

Re: how to remove lines form right keep

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

Not applicable

Re: how to remove lines form right keep

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

Not applicable

Re: how to remove lines form right keep

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

Not applicable

Re: how to remove lines form right keep

Hi Praveen,

Please mark correct answer, if your issue is solved.

Regards,

Shruti

Not applicable

Re: how to remove lines form right keep

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,,

Community Browser