Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
AWS Degraded - You may experience Community slowness, timeouts, or trouble accessing: LATEST HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Starting a CrossTable


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 NoStart Year-MonthHours Per MonthNo of Months
A1501103
B150215

4

C1503123

Table B
Year MonthOpp NoHours
1501A10
1502A10
1503A10
1502B15
1503B15
1504B15
1505B15
1503C12
1504C12
1505C12
1 Solution

Accepted Solutions
MarcoWedel

like in this case:

QlikCommunity_Thread_145664_Pic2.JPG

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

View solution in original post

10 Replies
ecolomer
Master II
Master II

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
Champion III
Champion III

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

Iter.png

Regards,

Anand

Not applicable
Author

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

Not applicable
Author

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

Thanks

Mark

its_anandrjs
Champion III
Champion III

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:

QlikCommunity_Thread_145664_Pic1.JPG

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

like in this case:

QlikCommunity_Thread_145664_Pic2.JPG

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

Not applicable
Author

Thanks Marco, this worked a treat.

Not applicable
Author

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