how to load and link a new excel file with a cyclic group field

[Anonymous]

Hi, I am trying to load and link an excel budget file that pretty much looks like below:

BU/Segment      Target

A                         100

1                          100

A being a BU, 1 being a segment, of which these 2 fields are under a cyclic group in the main pivot or straight table.There are a total of 5 different BU's and segments. Any help is greatly appreciated!

[JonnyPoole]

I took some liberties to create a data. I assumed the 5 other BU/SEGMENT values are reported in 2 line pairs with BU on the first line and Segment on the next...

This load routine will create a flat table with BU and Segment as seperate fields. You can then create a cycle group with both fields if that is what you need.

Temp:

LOAD

    rowno() as RecordNumber,

  if( mod( RecNo() , 2) <> 0, [BU/Segment], Previous([BU/Segment]) ) as [BU],

     Target

FROM

(ooxml, embedded labels, table is Sheet1)

;

NoConcatenate

Data:

load BU,

  Target,

  RowNo() as RecordNumber

Resident Temp

where mod(Recno(),2) <>0;

drop table Temp;

left join (Data)

load

  RowNo() as RecordNumber,

  [BU/Segment] as Segment

FROM

(ooxml, embedded labels, table is Sheet1)

where  mod(RecNo(),2)=0;

drop field RecordNumber;

[Anonymous]

Hi Jonathan, this works great! but the targets in my excel file tho are grouped by bu and segment say for example:

The result came out wrong.. because for whatever reason the recno() is not counting properly?

Appreciate your help!

BU/Segment	Target
A	100
B	100
C	100
D	100
E	100
F	100
1	100
2	100
3	100
4	100
5	100

so i changed the scripts to below:

Temp:

LOAD

    rowno() as RecordNumber,

   if( RecNo() < 8, [BU/Segment], Previous([BU/Segment]) ) as [BU],

     Targets

FROM

(ooxml, embedded labels, table is Sheet1)

;

NoConcatenate

Data:

load BU,

  Targets,

  RowNo() as RecordNumber

Resident Temp

where Recno() <8;

drop table Temp;

left join (Data)

load

  RowNo() as RecordNumber,

  [BU/Segment] as End_Mkt_Segment

FROM

(ooxml, embedded labels, table is Sheet1)

where  RecNo()>= 8;

drop field RecordNumber;

[JonnyPoole]

Hi there, yes the original solution would not work because it expects the odd rows to be a BU and the even rows to be the Segment. Also, the 2 records form duets. So the row 1 is the BU for the segment in row 2 . And row3 is the BU for the segment in row4 etc...

With the different structure can you explain how a segment relates to a specific BU ?  How do you differentiate a Segment from a BU ... for example when it transitions.  That is important in order to develop a solution.

[Anonymous]

Hi, thanks.. Why wouldn't recno() work in this case? It' should just count the number of records, no?

TIA!

[JonnyPoole]

Recno() counts the records in a load but doesn't take into account the where clause.

I used a where clause  in my first example to bring back only the odd rows. In that case Recno()  brings back 1,3,5 etc...

The same query using rowno() would take into account the where clause and bring back 1,2,3 etc... For the same records.

Make sense? 

[Anonymous]

Hi, thanks.. just wanted to clarify - the original solution was to differentiate the 1's and 0's, correct? (please see below) and the 1's grouped to BU, whilst the 0's grouped to Segment? I tried doing the same way by alternating the BU and segment in my excel file to load in too but it doesn't work...

Thank you so much for your time!

[JonnyPoole]

Hi

Here is a new shortened version of the script that deals with alternating BU/Segment.

Again, it expects the first row to be  a BU for the segment in the 2nd row,  BU in the 3rd row is the BU for the segment in the 4th row and on and on...

first i load the fields but i create an ID field to denote each 'BU/segment' couplet.  ceil(Recno()/2) does the division of the row by 2 and then rounds up.  So row 1 and row 2 will evaluate to '1'.  Row 3 and row 4 will evaluate to '2' and so on. So now that gives me a handy key.

then i build a new table by first pulling in the BUs only. I do that by creating a BU vs Segment flag in the first load that uses the orginal technique of figuring out what is an odd vs even row. odd rows are BUs, even rows are segment.

then i left join the segments to the BU on the ID field.

i know we have meandered around in this thread but please let me know if this is what you are looking for . i hope it is but let me know either way.

temp:

LOAD

  ceil(RecNo() / 2) as [BU-Segment-ID],

  if(mod( RecNo(),2) <>0, 'BU','Segment') as [BU-Segment-Flag],

  [BU/Segment],

     Target

FROM

(ooxml, embedded labels, table is Sheet1);

//load BUs

Data:

load

  [BU-Segment-ID],

  [BU/Segment] as [BU],

  Target

resident temp

where [BU-Segment-Flag]='BU' ;

left join (Data)

load

  [BU-Segment-ID],

  [BU/Segment] as Segment

resident temp

where [BU-Segment-Flag]='Segment';

drop table temp;

[Anonymous]

Hi, I have tried to apply the logic on the excel and this is what i came up with... I tried applying the logic to my scripts but it only worked on BU, the targets that show up on the segments are the BU numbers instead of segment's?

Again, thank you so much for your time and help.

[JonnyPoole]

Yes - the script only loads the targets from the BU because i noticed in the original sample that the target value for a BU was always the same as that for a Segment.  If they are not then we can have both targets in there too by using this updated version.  Then you will have 2 fields:  'BU Target' and 'Segment Target'  . in the chart you can add them together if you want in an expression or use individually. 

temp:

LOAD

  ceil(RecNo() / 2) as [BU-Segment-ID],

  if(mod( RecNo(),2) <>0, 'BU','Segment') as [BU-Segment-Flag],

  [BU/Segment],

     Target as [BU Target]

FROM

(ooxml, embedded labels, table is Sheet1);

//load BUs

Data:

load

  [BU-Segment-ID],

  [BU/Segment] as [BU],

  [BU Target]

resident temp

where [BU-Segment-Flag]='BU' ;

left join (Data)

load

  [BU-Segment-ID],

  [BU/Segment] as Segment,

  Segment as [Segment Target]

resident temp

where [BU-Segment-Flag]='Segment';