topic Re: Concatenate issue in QlikView

Concatenate issue

Wed, 08 Jul 2015 19:54:31 GMT

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‌

Re: Concatenate issue

Qrishna — Wed, 08 Jul 2015 20:18:03 GMT

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.

Re: Concatenate issue

Wed, 08 Jul 2015 20:39:29 GMT

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

Re: Concatenate issue

Qrishna — Wed, 08 Jul 2015 20:45:34 GMT

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.

Re: Concatenate issue

Wed, 08 Jul 2015 20:56:47 GMT

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];

Re: Concatenate issue

Qrishna — Wed, 08 Jul 2015 21:05:09 GMT

/*-----------------------------------------------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];

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

Re: Concatenate issue

Thu, 09 Jul 2015 13:14:33 GMT

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

Re: Concatenate issue

Thu, 09 Jul 2015 14:10:12 GMT

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.

Re: Concatenate issue

Qrishna — Thu, 09 Jul 2015 14:15:34 GMT

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.

Re: Concatenate issue

Thu, 09 Jul 2015 14:20:14 GMT

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

Re: Concatenate issue

Qrishna — Thu, 09 Jul 2015 14:30:10 GMT

could you please attach whatever excel files you are using in the application with some data.(say 50 lines in each excel) so that i can do some trial and error techniques?

Re: Concatenate issue

Thu, 09 Jul 2015 14:41:53 GMT

I can attach one but editing all of them so that I can attach them here would take a very long time... I am using excel files that are pulled daily so there are files from every single da

Re: Concatenate issue

Qrishna — Thu, 09 Jul 2015 14:50:47 GMT

Ok No worries. Give me some time. Will figure out something.

Re: Concatenate issue

Qrishna — Thu, 09 Jul 2015 16:11:00 GMT

Alex,

Please See the script from the attached doc.

Re: Concatenate issue

Thu, 09 Jul 2015 18:35:11 GMT

Is there a reason you made all of them named [Aged Orders - April]? I'm just curious

Re: Concatenate issue

Qrishna — Thu, 09 Jul 2015 18:54:03 GMT

Oh My bad... Every month should have its own name except the parent table.

Parent table:

march : [Aged Orders_temp] and [Aged Orders]

Child: (you concatenate child to parent to get the final table after reload)

April : [Aged Orders - April_temp] and [Aged Orders - April]

May : [Aged Orders - may_temp] and [Aged Orders - may]

june: [Aged Orders - jun_temp] and [Aged Orders - jun] etc etc

Re: Concatenate issue

Qrishna — Thu, 09 Jul 2015 19:24:38 GMT

let me know if everythings going well.

Re: Concatenate issue

Thu, 09 Jul 2015 20:25:57 GMT

I am trying to fix a few different things at once so I will let you know!!