Compare This year and last Year Products by flag

[New-Qlik]

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

[JordyWegman]

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

[New-Qlik]

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

[JordyWegman]

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

[New-Qlik]

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;

[JordyWegman]

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

[New-Qlik]

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;

[JordyWegman]

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

[kaanerisen]

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:

Hope it helps...

[New-Qlik]

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)