Concatenate issue

[#1]

So I am concatenating my tables together because I was ending up with synthetic keys that were actually messing up my data. But now, the script you gave to me isn't working. It was fine up until I concatenated them, and now it's coming up with a wrong answer. Am I putting the concatenation in the wrong place?

krishnachaitanya‌

[krishna_2644]

See attached.

reload it.

Also i made some comments,please read them and modify the script accordingly.

Let me know if that doesnt work.

and the scripting in the whole document should be simplified i guess, as you are using many unwanted fields, slow performance expressions etc.

[#3]

Sorry, I am a little confused about the note you left in there... What am I renaming?

[krishna_2644]

you are concatenating the April's ,May's tables to March table.

So when you reload the application you will see the table name as March---.

So what i am saying is rename the parent table to [Aged Data] (here parent table is [March Aged data]) and concatenate all the child tables (april,May's etc) to the  [Aged Data].

After you reload , you see the table name as [Aged Data] which has all the months data.

[#5]

Just to make sure, is this what you mean?

[Aged Orders - April]:

concatenate ([Aged Orders])

load *,

if(([Unique Order No.])=Peek([Unique Order No.]) and [Status]<>peek([Status]), 1, 0) as StatusChanged,

if(([Unique Order No.])=Peek([Unique Order No.]) and [Promise Date]<>Peek([Promise Date]), 1, 0) as PromiseChanged

Resident [Aged Orders - April_temp]

order by [Unique Order No.], TXTDate;

drop Table [Aged Orders - April_temp];

[krishna_2644]

/*-----------------------------------------------March Data-------------------------------------------------------*/

[Aged Orders - March_temp]:                                        

LOAD Date(Date#(Mid(FileName(),6,6),'MMDDYY')) as TXTDate,

//Above line is for pulling date from file name

     [Prom Days Out],

     [Sales Doc.],

     Item,  

  Upper(Status) as Status,

     Material as %Material,

     Material,

     Req.dlv.dt,

     [Deliv. dat],

     Plnt,

     [Order quantity],

     [Promise Date],

     [Sales Doc.] & Item as [Unique Order No.],

     [Sls Org],

     [Cat#],

     [Material Code],

     [Open Order($)],

     [On Delivery($)],

     [Not On Del ($)]

FROM

[\\gnrfdfp001\Purchasing\Supply Chain Reports\Aged Shortage Report By Plant\2015 Files\Aged 03*.xlsx]

(ooxml, embedded labels, header is 4 lines, table is [Aged All]);

[Aged Orders]:

load *,

if(([Unique Order No.])=Peek([Unique Order No.]) and [Status]<>Peek([Status]), 1, 0) as StatusChanged,

if(([Unique Order No.])=Peek([Unique Order No.]) and [Promise Date]<>Peek([Promise Date]), 1, 0) as PromiseChanged

Resident [Aged Orders - March_temp]

order by [Unique Order No.], TXTDate;

drop Table [Aged Orders - March_temp];

/*---------------------------------------------------------------------------------------------------------------------*/

/*-----------------------------------------------April Data-------------------------------------------------------*/

[Aged Orders - April_temp]:

Noconcatenate

LOAD Date(Date#(Mid(FileName(),6,6),'MMDDYY')) as TXTDate,

//Above line is for pulling date from file name

     [Prom Days Out],

     [Sales Doc.],

     Item,  

  Upper(Status) as Status,

     Material as %Material,

     Material,

     Req.dlv.dt,

     [Deliv. dat],

     Plnt as Plant,

     [Order quantity],

     [Promise Date],

     [Sales Doc.] & Item as [Unique Order No.],

     [Sls Org],

     [Cat#],

     [Material Code],

     [Open Order($)],

     [On Delivery($)],

     [Not On Del ($)],

     [Name]

FROM

[\\gnrfdfp001\Purchasing\Supply Chain Reports\Aged Shortage Report By Plant\2015 Files\Aged 04*.xlsx]

(ooxml, embedded labels, header is 4 lines, table is [Aged All]);

[Aged Orders - April]:

concatenate ([Aged Orders])

load *,

if(([Unique Order No.])=Peek([Unique Order No.]) and [Status]<>peek([Status]), 1, 0) as StatusChanged,

if(([Unique Order No.])=Peek([Unique Order No.]) and [Promise Date]<>Peek([Promise Date]), 1, 0) as PromiseChanged

Resident [Aged Orders - April_temp]

order by [Unique Order No.], TXTDate;

drop Table [Aged Orders - April_temp];

/*---------------------------------------------------------------------------------------------------------------------*/

/*-----------------------------------------------May Data-------------------------------------------------------*/

[Aged Orders - May_temp]:

Noconcatenate

LOAD Date(Date#(Mid(FileName(),6,6),'MMDDYY')) as TXTDate,

//Above line is for pulling date from file name

     [Prom Days Out],

     [Sales Doc.],

     Item,  

  Upper(Status) as Status,

     Material as %Material,

     Material,

     Req.dlv.dt,

     [Deliv. dat],

     Plnt as Plant,

     [Order quantity],

     [Promise Date],

     [Sales Doc.] & Item as [Unique Order No.],

     [Sls Org],

     [Cat#],

     [Open Order($)],

     [On Delivery($)],

     [Not On Del ($)],

     [Name]

FROM

[\\gnrfdfp001\Purchasing\Supply Chain Reports\Aged Shortage Report By Plant\2015 Files\Aged 05*.xlsx]

(ooxml, embedded labels, header is 4 lines, table is [Aged All]);

[Aged Orders - May]:

concatenate ([Aged Orders])

load *,

if(([Unique Order No.])=Peek([Unique Order No.]) and [Status]<>peek([Status]), 1, 0) as StatusChanged,

if(([Unique Order No.])=Peek([Unique Order No.]) and [Promise Date]<>Peek([Promise Date]), 1, 0) as PromiseChanged

Resident [Aged Orders - May_temp]

order by [Unique Order No.], TXTDate;

drop Table [Aged Orders - May_temp];

/*---------------------------------------------------------------------------------------------------------------------*/

[#7]

It still created the synthetic keys when I reloaded it all... I am not sure why.

[#8]

Okay, so I took the NoConcatenate out of the April data and it worked correctly, but then I tried adding May to it by doing the same thing and concatenating it to Aged Orders as well, but that messed up the way it counts Status again. So it only works when I only load the first two months of data as of now.

[krishna_2644]

Hi Alex,

Dont concatenate the temp tables([Aged Orders - April_temp] etc) in your scenario, as they are used just load the data from source.That is why 'NoConcatenate' is being used.

Concatenate only the tables (([Aged Orders - April],([Aged Orders - may] etc).

And make sure when you concatenate, you have the same field names in the tables other wise you may land up somewhere.

[#10]

I believe that is what I did (using the script you posted a few messages back) and that is when I received Synthetic keys again. For May, should I say Concatenate ([Aged Orders- April]) instead of Concatenate ([Aged Orders])?