Starting a CrossTable

[#1]

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

[ecolomer]

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))

));

[its_anandrjs]

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

[#4]

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

[#5]

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

Thanks

Mark

[its_anandrjs]

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

[MarcoWedel]

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

[MarcoWedel · Accepted solution]

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

[#9]

Thanks Marco, this worked a treat.

[#10]

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