topic Re: scripting in QlikView

scripting

Anonymous — Tue, 17 Oct 2017 11:18:58 GMT

Hi All,

How to achieve below scenario. for nov initially 3names are there then we have to check out of these 3 how many are present in dec ,then now for dec its 2 . so for jan we have to check from dec . so output is 2 . now for feb we have to check in jan and output is 1. similarly for other months

Input
year	month	name
2011	Nov	a
2011	Nov	b
2011	Nov	c
2011	Dec	b
2011	Dec	e
2011	Dec	c
2012	Jan	b
2012	Jan	c
2012	Feb	d
2012	Feb	b


output
	Nov_2011	Dec_2011	Jan_2012	Feb_2012
Nov_2011	3	2	2	1
Dec_2011		3	2	1
Jan_2012			2	1
Feb_2012				2
	3	5	6	5

Re: scripting

effinty2112 — Tue, 17 Oct 2017 13:33:28 GMT

Hi Kulwant,

Try:

Data:

LOAD

Date#(year&Upper(month),'YYYYMMM') as Month,

name;

LOAD * INLINE [

year, month, name

2011, Nov, a

2011, Nov, b

2011, Nov, c

2011, Dec, b

2011, Dec, e

2011, Dec, c

2012, Jan, b

2012, Jan, c

2012, Feb, d

2012, Feb, b

];

For i = 1 to FieldValueCount('Month')

For j = $(i) to FieldValueCount('Month')

Let vMonth1 = FieldValue('Month',$(i));

Let vMonth2 = FieldValue('Month',$(j));

Temp:

LOAD

name

Resident Data Where Month = '$(vMonth1)';

Inner Join

LOAD

name

Resident Data Where Month = '$(vMonth2)';

Count:

LOAD

Count (name) as Cnt Resident Temp;

Let vCount = Peek('Cnt',-1,'Count');

Drop Tables Temp,Count;

Result:

LOAD

'$(vMonth1)' as Month1,

'$(vMonth2)' as Month2,

'$(vCount)' as Count

AutoGenerate 1;

Next j;

Next i;

Giving:

Month1	Month2	2011NOV	2011DEC	2012JAN	2012FEB
2011NOV		3	2	2	1
2011DEC			3	2	1
2012JAN				2	1
2012FEB					2

Cheers

Andrew

Re: scripting

Anonymous — Wed, 18 Oct 2017 05:01:27 GMT

Hi Andrew

how to make chart which you have made final output?? what is the expression you are using??

Thanks and Regards

Re: scripting

effinty2112 — Wed, 18 Oct 2017 07:11:39 GMT

Hi Kulwant,

The chart is a pivot table with dimensions Month1 & Month2. Expression is Count or Sum(Count) if you want partial sums on Month1 like:

Month1	Month2	2011NOV	2011DEC	2012JAN	2012FEB
2011NOV		3	2	2	1
2011DEC			3	2	1
2012JAN				2	1
2012FEB					2
Total		3	5	6	5

I'm not sure how meaningful the sums are.

Regards

Andrew

Re: scripting

antoniotiman — Wed, 18 Oct 2017 08:00:02 GMT

May be this

Temp:
LOAD year,month,MonthName(MakeDate(year,Month(Date#(month,'MMM')))) as MonthName,name
Inline [
year month name
2011 Nov a
2011 Nov b
2011 Nov c
2011 Dec b
2011 Dec e
2011 Dec c
2012 Jan b
2012 Jan c
2012 Feb d
2012 Feb b
](delimiter is spaces);

Join
LOAD name,MonthName as MonthName1
Resident Temp;