Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

avneet85
Contributor

Compare This year and last Year Products by flag

HI,

 

I have to create comparable flag, so if an item present in previous year flag should say 'Yes' and if an item is not present in Previous year(that means new item) than flag should say 'No'

Product_table :

Load

Product_id,

Amount,

Fiscal_year

from Product 

Left join 

Load 

Product_id,

Fiscal_year -1,

if(Sum(amount)> 0 , 'Yes', 'No') as Comparable_Flag

Resident Product

Group By 

Product_id,

Fiscal_year -1;

 

This flag works fine for Yes condition but for no all the items should come.

But instead of going to 'No' part of condition its goes to  '_'(Null).

 

How to make no working fine?

 

Regards

AT

 

 

 

Labels (1)
12 Replies
Partner
Partner

Re: Compare This year and last Year Products by flag

Hi Avneet,

Try this:

Product_table:
Load
  Product_id,
  Amount,
  Fiscal_year
from Product 

Left join (Product_table)
Load 
  [Product_id],
  [Fiscal_year -1],
  Amount as Amount_LY
Resident Product

FinalTable:
Load
  [Product_id],
  [Fiscal_year],
  [Fiscal_year -1],
  Amount,
  Amount_LY
  IF(Isnull(Amount_LY) and Sum(Amount)>0,'Yes','No') as Flag
Resident Product_table;

Drop table Product_table;

Jordy

Climber

 

Work smarter, not harder
avneet85
Contributor

Re: Compare This year and last Year Products by flag

Hi Jordy,

 

MY query keep on running around left outer join , its just loading so something is wrong . I have rename Fiscal_year as Fiscal_year_LY

Left join (Product_table)
Load
  [Product_id],
  [Fiscal_year -1] as Fiscal_year_LY,
  Amount as Amount_LY
Resident Product

Partner
Partner

Re: Compare This year and last Year Products by flag

Can you show the exact script you are using? Because the one you send, and I changed, still contains a lot of defects.

Jordy

Climber

Work smarter, not harder
avneet85
Contributor

Re: Compare This year and last Year Products by flag

Its same as your script I just I have named [Fiscal Year -1] as [Fiscal Year LY] in left join condition. I think you missed

 

Product_table:
Load
  Product_id,
  Amount,
  Fiscal_year
from Product

Left join (Product_table)
Load
  [Product_id],
  [Fiscal_year -1] as [Fiscal year LY],
  Amount as Amount_LY
Resident Product

FinalTable:
Load
  [Product_id],
  [Fiscal_year],
  [Fiscal_year -1],
  Amount,
  Amount_LY
  IF(Isnull(Amount_LY) and Sum(Amount)>0,'Yes','No') as Flag
Resident Product_table;

Drop table Product_table;

Partner
Partner

Re: Compare This year and last Year Products by flag

I saw it, but I wanted to make sure that it is. Because this script is not completely correct. How does your statement for 'from Product' look like? Because this statement doesn't work and I don't know what your real connection is... probably something with //Lib: etc.

Try this, but change your connections (from Product / Resident product):

Product_table:
Load
  [Product_id],
  [Amount],
  [Fiscal_year]
from Product; 

Left join (Product_table)
Load 
  [Product_id],
  [Fiscal_year -1] as [Fiscal_Year_LY],
  [Amount] as [Amount_LY]
Resident Product;

FinalTable:
Load
  [Product_id],
  [Fiscal_year],
  [Fiscal_year_LY],
  Amount,
  Amount_LY,
  IF(Isnull(Amount_LY) and Sum(Amount)>0,'Yes','No') as Flag
Resident Product_table;

Drop table Product_table;

Jordy

Climber

Work smarter, not harder
avneet85
Contributor

Re: Compare This year and last Year Products by flag

Hi,

MY scripts looks like this , I got Error 129 now . Seems its doing Cartesian product

Product_table:
Load
  [Product_id],
  [Amount],
  [Fiscal_year]
from FROM [lib://...\QVD_Product.qvd](qvd);

Left join (Product_table)
Load
  [Product_id],
  [Fiscal_year -1] as [Fiscal_Year_LY],
  [Amount] as [Amount_LY]
Resident Product_table;

FinalTable:
Load
  [Product_id],
  [Fiscal_year],
  [Fiscal_year_LY],
  Amount,
  Amount_LY,
  IF(Isnull(Amount_LY) and Sum(Amount)>0,'Yes','No') as Flag
Resident Product_table;

Drop table Product_table;

Partner
Partner

Re: Compare This year and last Year Products by flag

Hi Avneet,

The problem =129 - is often a RAM problem. Can you check your machine while doing the left join?

But your script can't work a.t.m. because you are trying to retrieve information that..

  1. Should be already in your first table, because you are in your 2nd table resident loading and left joining again.. 
    1. Amount and Amount_LY are now just the same
    2. There is no Fiscal_year-1 in your Product table (at least not where you load it, or you should add it)
  2. Is coming from the wrong resident load and shouldn't be Product_table.

Do you understand the problem here?

Jordy

Climber

Work smarter, not harder
kaanerisen
Contributor III

Re: Compare This year and last Year Products by flag

Hi avneet85,

You don't have to make joins to check if the records exist on previous year. It will be time consuming on larger datasets.(depends on data structure).

You can use lookup function to look into the current table to check if record exists.

Here is a sample script:

load
*,
  IF(isnull(Lookup('Product','PRYEAR',PRYEARPRE)),'NO','YES') as FLG;
load 
  Product&'-'&TEXT(Year) as PRYEAR,
  Product&'-'&TEXT(NUM(Year)-1) as PRYEARPRE,
  *
Inline [
Product,Year,Amount
A,2016,3000
A,2017,4000
B,2016,2000
B,2018,1000
B,2019,3000
C,2017,8000
C,2018,9000
C,2019,4000
];

DROP FIELDS PRYEARPRE,PRYEAR;

Output:

Untitled.png

Hope it helps...

avneet85
Contributor

Re: Compare This year and last Year Products by flag

HI Kaanerisen,

 

I tried same but it gives me 'NO' as flag for all Products.

Product:
Load
*,
IF(isnull(Lookup('Product','ProductYear',ProductPrevYear)),'No', 'YES') as FLG;
load
Product&'-'&TEXT([Fiscal Year]) as ProductYear,
Product&'-'&TEXT(NUM([Fiscal Year])-1) as ProductPrevYear,

Product,

Amount,

[Fiscal Year]

from 

FROM [lib://...\QVD_Product.qvd](qvd)