LOAD syntax error

[sculptorlv]

Hello please help me with the syntax, what I am doing wrong?

DATA_DELIVERY_STATISTICS:

     SQL SELECT ...

     FROM .....

     ;

DATA_DELIVERY_STATISTICS_MONTHLY:

     LOAD

          Driver_ID,

          Order_Number,

          Item_Quantity,

          Location_Code,

          MonthName(Delivery_Date) AS Delivery_Date

     RESIDENT DATA_DELIVERY_STATISTICS

     ;

DATA_DELIVERY_QUANTITY:

     LOAD

          Driver_ID,

          Delivery_Date,

          SUM(Item_Quantity) AS Item_Quantity_Sum_Monthly

     RESIDENT DATA_DELIVERY_STATISTICS_MONTHLY

     GROUP BY

          Driver_ID,

          Delivery_Date

     ;

DROP TABLE DATA_DELIVERY_STATISTICS;

DROP TABLE DATA_DELIVERY_STATISTICS_MONTHLY;

And I got mistake about last load:

"Table not found

DATA_DELIVERY_QUANTITY:"

[Anil_Babu_Samineni]

Because, you are dropping the table where Resident with Aggregation. Can you try this?

DATA_DELIVERY_STATISTICS:

     SQL SELECT ...

     FROM .....     ;

DATA_DELIVERY_STATISTICS_MONTHLY:

     LOAD

          Driver_ID,

          Order_Number,

          Item_Quantity,

          Location_Code,

          MonthName(Delivery_Date) AS Delivery_Date

     RESIDENT DATA_DELIVERY_STATISTICS     ;

NoConcatenate

DATA_DELIVERY_QUANTITY:

     LOAD

          Driver_ID,

          Delivery_Date,

          SUM(Item_Quantity) AS Item_Quantity_Sum_Monthly

     RESIDENT DATA_DELIVERY_STATISTICS_MONTHLY

     GROUP BY  Driver_ID,   Delivery_Date ;

DROP TABLE DATA_DELIVERY_STATISTICS;

DROP TABLE DATA_DELIVERY_STATISTICS_MONTHLY;

[arvind_patil]

Hi Ruslans,

Use No concate inbetween and reload:

DATA_DELIVERY_STATISTICS:

     SQL SELECT ...

     FROM .....

     ;

NoConcatenate

DATA_DELIVERY_STATISTICS_MONTHLY:

     LOAD

          Driver_ID,

          Order_Number,

          Item_Quantity,

          Location_Code,

          MonthName(Delivery_Date) AS Delivery_Date

     RESIDENT DATA_DELIVERY_STATISTICS

     ;

NoConcatenate

DATA_DELIVERY_QUANTITY:

     LOAD

          Driver_ID,

          Delivery_Date,

          SUM(Item_Quantity) AS Item_Quantity_Sum_Monthly

     RESIDENT DATA_DELIVERY_STATISTICS_MONTHLY

     GROUP BY

          Driver_ID,

          Delivery_Date

     ;

DROP TABLE DATA_DELIVERY_STATISTICS;

DROP TABLE DATA_DELIVERY_STATISTICS_MONTHLY;

And  check this code. It will work.

Thanks,

Arvind Patil

[sculptorlv · Accepted solution]

Guys, I don't get it..

I have error on NoConcatenate function. All other code is the same. What should I do, can you please concertize?

Thank you in advance

[sculptorlv]

I commented last row:

//DROP TABLE DATA_DELIVERY_STATISTICS_MONTHLY;

and got the same error, I guess it is not about drop function for aggregated table.

[antoniotiman]

Hi Ruslans,

if DATA_DELIVERY_STATISTICS and DATA_DELIVERY_STATISTICS_MONTHLY have the same fields

then QV Concatenate these.

If You Use NoConactenate You get Synthetics Key.

You can

MonthName(Delivery_Date) AS Delivery_Date_Month

or

Resident DATA_DELIVERY_STATISTICS instead DATA_DELIVERY_STATISTICS_MONTHLY

Ctrl+T make You see Tables You are loading.

Regards,

Antonio

[techvarun]

Please share sample data

[Anil_Babu_Samineni]

If i want to work on this, I would prefer to use this

DATA_DELIVERY_STATISTICS:

Load *;

     SQL SELECT ...

     FROM .....     ;

Left Join (DATA_DELIVERY_STATISTICS)

     LOAD

          Driver_ID,

          Order_Number,

          Sum(Item_Quantity) as Item_Quantity,

          Location_Code,

          MonthName(Delivery_Date) AS Delivery_Date

     RESIDENT DATA_DELIVERY_STATISTICS   Group By <All Non Aggregate dimensions>  ;

[sculptorlv]

I found out, that this code works!! But still this funcion is highlighted:

[sculptorlv]

Thanks!

I will do this ..