scripting

[Anonymous]

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

[effinty2112 · Accepted solution]

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

[Anonymous]

Hi Andrew

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

Thanks and Regards

[effinty2112]

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

[antoniotiman]

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;