topic Re: excluding records in the script (help needed urgently) in QlikView

excluding records in the script

Tue, 14 Jun 2016 10:23:13 GMT

Hello All,

I have a product_id for which want to only include data till 2015,

but for 2016 i dont want to include that in the script.

for example only include data for product_id 126 till 2015 not in 2016.

How can i do this in the script?

Please help.

Thanks & Regards,

Jaya

Re: excluding records in the script (help needed urgently)

sunny_talwar — Tue, 14 Jun 2016 10:36:51 GMT

May be like this:

LOAD *

FROM Source

Where not (Product_id = 126 and Year = 2016);

Re: excluding records in the script (help needed urgently)

MayilVahanan — Tue, 14 Jun 2016 10:42:43 GMT

Try like this

Load * from yourtablename where Year < 2016 and product_id = 126;

Re: excluding records in the script (help needed urgently)

Tue, 14 Jun 2016 10:50:49 GMT

Thanks Sunny for the reply.

the year feild is in the calender table which is being loaded late based on a order_date.

in the source table there is the product_id and order_id.

How do i do this?

Re: excluding records in the script (help needed urgently)

MayilVahanan — Tue, 14 Jun 2016 10:54:25 GMT

Then try like this

Load * from yourtablename where Year(order_date) < 2016 and product_id = 126;

Re: excluding records in the script (help needed urgently)

Tue, 14 Jun 2016 11:09:47 GMT

Thanks Mayil.

Can i also do it in the straight table?

Re: excluding records in the script (help needed urgently)

sunny_talwar — Tue, 14 Jun 2016 11:23:51 GMT

Or this:

LOAD *

FROM Source

Where not (Product_id = 126 and Year(order_date) = 2016);

Re: excluding records in the script (help needed urgently)

Tue, 14 Jun 2016 11:29:06 GMT

Thanks sunny for your help as always.

But can we also put this condition on in the expression in the straight table??

Re: excluding records in the script (help needed urgently)

Tue, 14 Jun 2016 11:59:39 GMT

Hello Sunny,

Can i use the below expression.?

how do i include Year=2016 in the below expression?

aggr(if(match(product_id,'126')=0, product_id),product_id)

Appreciate your help.

Regards,

Jaya

Re: excluding records in the script (help needed urgently)

sunny_talwar — Tue, 14 Jun 2016 15:17:30 GMT

You can. May be like this in your expression

RangeSum(Sum({<Product_id -= {126}>}Measure), Sum({<Product_id = {126}, Year = {'<2016'}>} Measure))

Re: excluding records in the script (help needed urgently)

Thu, 16 Jun 2016 05:19:55 GMT

Hello Sunny,

The expression you gave does not work and i am not able to troubleshoot it.

please help.

I need to exclude all the records for product_id 126 for the year 2016 from the straight table.

Need help on this urgently.

Thanks & Regards,

Jaya

Re: excluding records in the script (help needed urgently)

Thu, 16 Jun 2016 06:45:18 GMT

Hello Sunny,

This expression works

count(distinct(aggr(only({<lproduct_id-={'956'}>}order_id),order_id)))

now i need to add the Year = 2016 field in this expression.

I am not able to figure that out.

Please help.

Appreciate your help as always.

Regards,

Jaya

Re: excluding records in the script (help needed urgently)

sunny_talwar — Thu, 16 Jun 2016 10:00:25 GMT

This?

Count(DISTINCT Aggr(Only({<lproduct_id-={'956'}, Year = {2016}>} order_id), order_id))

Count({<Year = {2016}>}DISTINCT Aggr(Only({<lproduct_id-={'956'}>} order_id), order_id))