Data load

[#1]

Dear Legends,

May I please get a little help on the scenario given below;

Tab1:

ITEMNO	Custs	QTY	Total
1	X	1	130
2	Y	2	50
3	Z	2	50

Tab2: (Same Items assigned to different values as Labels and no sales total in this table)

ITEMNO	Labels
1	A
2	B
3	C

What do I want to achieve is;

Tab3:

AllCusts	QTY	Total
X	1	130
Y	2	50
Z	2	50
A	3	100
B	3	100

I shall appreciate the precious time you could put on it.

Kind Regards,

Aamir

[#2]

Can you please paste your load query?

[jagan]

Hi,

On what basis the Qty and Total is assigned to A and B, if you just want 100 then simply use this

Tab1:

LOAD

*

FROM Tab1;

Tab2:

LOAD *

FROM Tab2

Tab3:

NoConcatenate

LOAD

*

RESIDENT Tab1;

Concatenate(Tab3)

LOAD

*, 

     3 AS QTY,

     100 AS Total

RESIDENT Tab2;

If you dont want Tab1 and Tab2 just drop it by using DROP TABLES Tab1, Tab2;

Hope this helps you.

Regards,

Jagan.

[#4]

Hi Anju,

Please find the load query below but I am sure it is not the right way to do it.

TAB1:

LOAD CUSTS,

    *ITEM as ITEMNO,

    Sale;

SQL CUSTS,

    ITEM,

    Sale

FROM LH.dbo."TAB1";

Tab2:

LOAD BRAND,

    COLOUR,

    DESIGN,

    *ITEMNO,

    LABEL;

SQL SELECT BRAND,

    COLOUR,

    DESIGN,

    ITEMNO,

    LABEL,

FROM LH.dbo."tab2";

ADD LOAD ITEMNO as ITEMS,

    LABEL as E_LABEL,

    if (wildmatch(LABEL, 'EXCO'), 'EXCO',

    if (WildMatch(LABEL,'*COMMERCIAL*'),'COMERCIAL','OTHERS')) as *CUSTS;

SQL SELECT ITEMNO,

    LABEL

FROM LH.dbo."TAB2";

[#5]

Hi,

You can add this under Tab2:

'LABEL AS CUSTS'

Next, for table 3, Create a pivot table with custs as dimension and add the other values under expression.

But, how are you calculating qty and total for customers A,B and C?

[#6]

Hi Aamir,

Jagan is right, if you want only 3 as QTY and 100 as Total for Cust A,B, then this will solve your purpose.

ResultTable:

LOAD

ITEMNO,

Custs as AllCusts,

QTY,

Total

FROM Tab1;

CONCATENATE

LOAD

ITEMNO,

Labels as AllCusts,

3 as QTY,

100 as Total

FROM Tab2

Hope this is helpful.

Thanks

Syed Haji