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!

[Anonymous]

Thanks! Can I ask where does Segment Target get defined, because when it's first loaded the target gets defined as BU Target...Thanks for your patience and time!

[JonnyPoole]

The [Segment Target] is attached to the qlik table in the last operation (left join).

in that step  i join the segment records to the BU records based  on the BU/Segment key. When doing the join, it is easy to bring an additional field like [target] and because i'm joining in only segment records, i know the targets are only segment targets. So in this operation i'm bringing the original targets for the segments into the table in qlik.

[Anonymous]

Thanks, not sure why.. but it keeps giving me script error - Segment not found..

[JonnyPoole]

d'oh !!

change

Segment as [Segment Target]

to

Target as [Segment Target]

(its near the bottom of script)

[Anonymous]

ohhhh... great... now it's not recognizing the target in the segment join scripts...

[JonnyPoole]

sorry about that.   needed more edits... here is an update

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 as [BU Target]

resident temp

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

left join (Data)

load

  [BU-Segment-ID],

  [BU/Segment] as Segment,

  Target as [Segment Target]

resident temp

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

[Anonymous]

i tried that just now but if i did the same edit you said just now, I am back to the same error as beginning where the segments copied the targets from BUs instead of their own.

[JonnyPoole]

you have amazing patience !  I forgot to add the 'drop table temp;' line at the bottom.  Hopefully this is it !

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 as [BU Target]

resident temp

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

left join (Data)

load

  [BU-Segment-ID],

  [BU/Segment] as Segment,

  Target as [Segment Target]

resident temp

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

drop table temp;

[Anonymous]

I think you have more patience than me.... it's not working. Segment still picks up BU targets...

Thank you for your patience

[JonnyPoole]

Here is the sample i was testing off and it works (according to what i think it should do).

can you check out the source file and the qvw and verify it is working as intended ?

if its working, send me your script and source file .  If it ISN'T working as you need to do , let me know what you need to change.