Qliksense dublicate rows

[krmvacar]

Hi all,

I have a excel and Here, if the previous material is equal, I want it to bring its price, but it gives me multiple rows. Can you help me?

Material	Order_code	Order_Date	Price
EI00500002	0450003500	27.03.2017	238,50
EI00500002	0450007121	28.04.2017	8.053,10
EI00500002	0450010480	02.06.2017	1.790,00
EI00500002	0450010559	05.06.2017	660,00
EI00500002	0450010559	05.06.2017	1.515,00

load 

Material,

Order_code,

Order_Date,

Price,

if(Material=peek(Material),peek(Order_Date)) as [previous price]

Resident Order order by Material,Order_Date;

Material	Order_code	Order_Date	Price	previous price
EI00500002	0450003500	27.03.2017	238,50	238.50
EI00500002	0450003500	27.03.2017	238,50	-
EI00500002	0450007121	28.04.2017	8.053,10	238.50
EI00500002	0450007121	28.04.2017	8.053,10	8,053.10
EI00500002	0450010480	02.06.2017	1.790,00	1,790.00
EI00500002	0450010480	02.06.2017	1.790,00	8,053.10
EI00500002	0450010559	05.06.2017	660,00	660.00
EI00500002	0450010559	05.06.2017	660,00	1,515.00
EI00500002	0450010559	05.06.2017	660,00	1,790.00
EI00500002	0450010559	05.06.2017	1.515,00	660.00
EI00500002	0450010559	05.06.2017	1.515,00	1,515.00

[vincent_ardiet_]

If you do something like this, this is working more or less. This is not duplicating rows, however, when you have 2 orders at the same date with different prices, which price should you use?

Order:
load * inline
[Material,Order_code,Order_Date,Price
EI00500002,0450003500,27.03.2017,238.50
EI00500002,0450007121,28.04.2017,8053.10
EI00500002,0450010480,02.06.2017,1790.00
EI00500002,0450010559,05.06.2017,660.00
EI00500002,0450010559,05.06.2017,1515.00
EI00500003,0450003500,27.03.2017,1238.50
EI00500003,0450007121,28.04.2017,9053
EI00500003,0450010480,02.06.2017,2790
EI00500003,0450010559,05.06.2017,760
EI00500003,0450010559,05.06.2017,2515
];

NewOrder:
load
Material,
Order_code,
Order_Date,
Price,
if(Material=peek(Material),peek(Price)) as [previous price]
Resident Order order by Material,Order_Date;

drop table Order;

[krmvacar]

Hi @vincent_ardiet_ 

It worked in Excel but not in my code.Duplicate continues to give.I can send the QVF file if you want

Thanks for your help

[vincent_ardiet_]

You can put your code, I will not be able to download your QVF.

[krmvacar]

I understand problem.I receive 2 different prices within one date.

I think if I make the peek field according to my key field, the problem will be solved.

How can I add it to the peek area? Can you help me?

load

Key_order
Material,
Order_code,
Order_Date,
Price,
if(Material=peek(Material),peek(Price)) as [previous price]
Resident Order order by Material,Order_Date;

Order:
load * inline
[Key_order,Material,Order_code,Order_Date,Price
41052220913,EI00500002,0450003500,27.03.2017,238.50
41052221113,EI00500002,0450007121,28.04.2017,8053.10
41052222213,EI00500002,0450010480,02.06.2017,1790.00
41052223913,EI00500002,0450010559,05.06.2017,660.00
41052224113,EI00500002,0450010559,05.06.2017,1515.00
41052226713,EI00500003,0450003500,27.03.2017,1238.50
41052228613,EI00500003,0450007121,28.04.2017,9053
41052229913,EI00500003,0450010480,02.06.2017,2790
41052220113,EI00500003,0450010559,05.06.2017,760
41052220413,EI00500003,0450010559,05.06.2017,2515
];

[vincent_ardiet_]

Maybe just with using this order by clause:
 order by Material,Order_Date,key_order;

[krmvacar]

its not working

[Digvijay_Singh]

So you got multiple prices per Material code, which price you want to use, highest price? lowest price?

I think without that decision this below code will keep producing multiple rows per material code 

if(Material=peek(Material),peek(Price)) as [previous price]

Also we normally use Previous() while making comparison in if condition and Peek for writing new columns - 

if(Material=Previous(Material),peek(Price)) as [previous price]