topic Re: Iteration or Permutation ? in QlikView

Iteration or Permutation ?

Harry12 — Wed, 04 Dec 2019 21:53:29 GMT

Hi guys,

Sorry for the newbie question. I had this following table

	Sale
12/2/2019	1
12/3/2019	2
12/4/2019	5
12/5/2019	9

Need to show the day over day progress :

	Sale	D1	D2	D3
12/2/2019	1	2.00	5.00	9.00
12/3/2019	2	5.00	9.00
12/4/2019	5	9.00
12/5/2019	9

Re: Iteration or Permutation ?

rwunderlich — Thu, 05 Dec 2019 00:11:44 GMT

I think the best approach may be using the Below() function as a chart expression.

The expression for D1 would be:

alt(below(Sum(Sale),1), '')

and for D2

alt(below(Sum(Sale),2), '')

Alternatively, you could generate D1/D2 etc in the script, but I would need to know if there are multiple rows per date or a single row per date.

The attached qvw demonstrates both techniques.

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

Re: Iteration or Permutation ?

Harry12 — Thu, 05 Dec 2019 12:39:40 GMT

Thanks Rob for the reply. Greatly appreciated.

Re: Iteration or Permutation ?

Harry12 — Thu, 05 Dec 2019 14:16:39 GMT

Hi Rob,

If I had multiple records on the same day, such as

		Sales	D1	D2	D3
12/2/2019	Store 1	1	3	5	6
12/2/2019	Store 2	2	4		7
12/3/2019	Store 1	3	5	6
12/3/2019	Store 2	4		7
12/4/2019	Store 1	5	6
12/5/2019	Store 1	6
12/5/2019	Store 2	7

Do I need to load each store into each own table so I can do RecNo properly ?

Re: Iteration or Permutation ?

rwunderlich — Tue, 17 Dec 2019 15:07:36 GMT

Hi, Sorry for the delayed response. If you have multiple rows per date, then you first need to group by date, either in a resident load or in the initial load.

LOAD F1 as BaseDate,
Sum(Sales) as Sale
FROM
[https://community.qlik.com/t5/QlikView-Scripting/Iteration-or-Permutation/td-p/1654352]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @3)
Group By F1
;

Example attached.

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com