CONCATENATE DATA

[#1]

Hi,

i have a question, i have two data source to be used. One is "Table A" and the other one is "Table B"

Table A consist of 4 field like this

Key     YearWeek     SalesQty

A1          1514              1

B1          1514              2

C1          1514              3

i want to concate Table A with Table B with this field,

Key     YearWeek     SalesQty

D1          1512             2

E1          1513               3

F1          1514               1

G1          1515               2

H1          1516               1

I want to make a concatenated Table that consist of

Key     YearWeek     SalesQty

A1          1514              1

B1          1514              2

C1          1514              3

D1          1512             2

E1          1513               3

F1          1514               1

Key G1 and H1 doesnt include because yearweek G1 and H1 is bigger than maximum YearWeek in Table A. How can i make a selection like this? I want to concatenate Data Table A with Data in table B that Smaller or equal with maximum yearweek in Table A.

Really need your help guys, Thanks

Regards,

Indra

[swuehl · Accepted solution]

TA:

LOAD * INLINE [

Key,     YearWeek,     SalesQty

A1,          1514,              1

B1,          1514,              2

C1,          1514,              3

];

Max:

LOAD max(YearWeek) as Max Resident TA;

Let vMax = peek('Max',0,'Max');

CONCATENATE (TA) LOAD * INLINE [

Key,     YearWeek,     SalesQty

D1,          1512,             2

E1,          1513,               3

F1,          1514,               1

G1,          1515,               2

H1,          1516,               1

]

WHERE YearWeek <= $(vMax);

[jonathandienst]

Code it like this:

TableA:

LOAD Key,

  YearWeek,

  SalesQty,

  ...

FROM TableA....;

T_Max:

LOAD Max(YearWeek) As MaxYW

Resident TableA;

Let zMaxYW = Peek('MaxYW');

DROP Table T_Max;

Concatenate(TableA)

LOAD Key,

  YearWeek,

  SalesQty,

  ...

FROM TableB...

Where YearWeek <= $(zMaxYW);

[jonas_rezende]

An alternative would also be:

Only use not variable.

TableA

LOAD Key,

     YearWeek,

     SalesQty

FROM TableA;

TempYearWeek:

LOAD

Max(YearWeek) as TempYearWeek

Resident TableA;

Concatenate(TableA)

LOAD Key,

     YearWeek,

     SalesQty

FROM

TableB

Where

YearWeek <= FieldValue('TempYearWeek',1);

DROP Table TempYearWeek;

[#5]

Dear Swuehl

Wow thanks swuehl, it solved my problem.

Sorry for late reply. Thanks