topic Re: Starting a CrossTable in QlikView

Starting a CrossTable

Sat, 13 Dec 2014 07:17:53 GMT

Hi not sure where to start from converting Table A into Table B, I know there is a cross table function, but whats the best way to use it. The real Table A will have 50,000 + rows and the Number of Months can be up to 240.

Table A
Opp No	Start Year-Month	Hours Per Month	No of Months
A	1501	10	3
B	1502	15	4
C	1503	12	3

Table B
Year Month	Opp No	Hours
1501	A	10
1502	A	10
1503	A	10
1502	B	15
1503	B	15
1504	B	15
1505	B	15
1503	C	12
1504	C	12
1505	C	12

Re: Starting a CrossTable

ecolomer — Sat, 13 Dec 2014 07:45:44 GMT

Is that?

The script

CrossTable([Start Year-Month], Datos)

LOAD [Opp No],

[Start Year-Month],

[Hours Per Month],

[No of Months]

FROM

[http://community.qlik.com/thread/145664]

(html, codepage is 1252, embedded labels, table is @1, filters(

Remove(Row, Pos(Top, 1))

));

Re: Starting a CrossTable

its_anandrjs — Sat, 13 Dec 2014 07:57:24 GMT

Hi,

Try this expression copy and paste this and check the load script for output

TableA:

LOAD * INLINE [

Opp No, Start Year-Month, Hours Per Month, No of Months

A, 1501, 10, 3

B, 1502, 15, 4

C, 1503, 12, 3

];

Left Join

LOAD

[Opp No],

Max([No of Months]) as [Max No of Months]

Resident TableA Group By [Opp No];

TableB:

LOAD

[Opp No],

[Hours Per Month] as Hour,

if([No of Months] <= [Max No of Months] ,[Start Year-Month] + IterNo()-1 ) as [Year Month],

Resident TableA While IterNo() <= [Max No of Months];

And i think you want this output

Regards,

Anand

Re: Starting a CrossTable

Sat, 13 Dec 2014 07:59:34 GMT

Hi Enrique

Thanks for your reply, that not quite what I'm after I may have not made it clear, it you take Opp B on table A it starts in Feb 2015 (1502) and uses 15 Hours for the next 4 Months so for Opp B I need to Build following

1502 15 Hrs

1503 15 Hrs

1504 15 Hrs

1505 15 Hrs

Re: Starting a CrossTable

Sat, 13 Dec 2014 08:01:50 GMT

Hi Anand this looks good I will give it a try.

Thanks

Mark

Re: Starting a CrossTable

its_anandrjs — Sat, 13 Dec 2014 08:04:56 GMT

Hi,

Check my reply on the post and there is another simple way is

TableA:

LOAD * INLINE [

Opp No, Start Year-Month, Hours Per Month, No of Months

A, 1501, 10, 3

B, 1502, 15, 4

C, 1503, 12, 3

];

TableB:

LOAD

[Opp No],

[Hours Per Month] as Hour,

[Start Year-Month] + IterNo()-1 as [Year Month],

Date(AddMonths(Date#([Start Year-Month],'YYMM'),IterNo()-1),'YYMM') as [New Year Month],

Resident TableA While IterNo() <= [No of Months];

This gives same output.

But if the [No Of MOnths} field has more than 12 months field then use this expression in load script i assume this

Date(AddMonths(Date#([Start Year-Month],'YYMM'),IterNo()-1),'YYMM') as [New Year Month],

Regards

Anand

Re: Starting a CrossTable

MarcoWedel — Sat, 13 Dec 2014 15:54:29 GMT

Hi,

to reflect possible year changes within table B, you could also try with:

TableB:
LOAD Date(AddMonths(Date#([Start Year-Month],'YYMM'),IterNo()-1),'YYMM') as [Year Month],
    [Opp No],
    [Hours Per Month] as Hours
FROM [http://community.qlik.com/thread/145664] (html, codepage is 1252, embedded labels, table is @1, filters(Remove(Row, Pos(Top, 1))))
While IterNo()<=[No of Months];

hope this helps

regards

Marco

Re: Starting a CrossTable

MarcoWedel — Sat, 13 Dec 2014 16:18:30 GMT

like in this case:

TableB:
LOAD Date(AddMonths(Date#([Start Year-Month],'YYMM'),IterNo()-1),'YYMM') as [Year Month],
    [Opp No],
    [Hours Per Month] as Hours
INLINE [
    Opp No, Start Year-Month, Hours Per Month, No of Months
    A, 1501, 10, 3
    B, 1502, 15, 4
    C, 1503, 12, 3
    D, 1511, 8, 4
    E, 1501, 6, 18
]
While IterNo()<=[No of Months];

hope this helps

regards

Marco

Re: Starting a CrossTable

Sun, 14 Dec 2014 05:54:29 GMT

Thanks Marco, this worked a treat.

Re: Starting a CrossTable

Sun, 14 Dec 2014 05:56:46 GMT

Thanks for your help Anand but couldn't work out the syntax problem on "Resident TableA While IterNo() <= [No of Months];"

Re: Starting a CrossTable

MarcoWedel — Sun, 14 Dec 2014 09:38:24 GMT

You're welcome

regards

Marco