topic Re: Qliksense dublicate rows in App Development

Qliksense dublicate rows

krmvacar — Thu, 11 Jan 2024 08:48:14 GMT

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

Re: Qliksense dublicate rows

vincent_ardiet_ — Thu, 11 Jan 2024 09:46:21 GMT

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;

Re: Qliksense dublicate rows

krmvacar — Thu, 11 Jan 2024 10:06:19 GMT

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

Re: Qliksense dublicate rows

vincent_ardiet_ — Thu, 11 Jan 2024 10:09:29 GMT

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

Re: Qliksense dublicate rows

krmvacar — Thu, 11 Jan 2024 11:05:34 GMT

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

Re: Qliksense dublicate rows

vincent_ardiet_ — Thu, 11 Jan 2024 14:35:12 GMT

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

Re: Qliksense dublicate rows

krmvacar — Thu, 11 Jan 2024 18:42:14 GMT

its not working

Re: Qliksense dublicate rows

Digvijay_Singh — Thu, 11 Jan 2024 20:22:04 GMT

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]