Script Problem

[#1]

Hi friends

i have table fields like below:

Customer:

load TRANSACTIONDATE,

        QTY

FROM CUSTOMER;

Here TRANACTIONDATE will be 02-JAN-12 05:15:50 etc.In Qty field,  i have to split as OpeningStockQty and ClosingStockQty.

The following date 02-JAN-12 00:00:00  Quantity as opening StockQty and 02-JAN-12 22:01:12 as ClosingStockQty.

how can i achieve this?

Please help me out .

Thanks in Advance

Suresh

[#2]

This is for calculating opening And Closing Stock Qty On the Same date

minimum timestamp  qty will be opening stock ,Maximum timestamp quantity will be Closing Stock qty on the Same date

[erichshiino]

Hi,  Suresh.

You may read your resident table twice and create a flag ( or two separed dimensions)

The script would be more or less like this :

// --------------------------------------------

Customer:

load TRANSACTIONDATE,

daystart( TRANSACTIONDATE) as DATE,

        QTY

FROM CUSTOMER;

Fact:

Load DATE, min ( TRANSACTIONDATE)  as Timestamp, 

QTY  as OpeningStock

Resident CUSTOMER

group by DATE;

Concatenate (Fact)

Load DATE, max( TRANSACTIONDATE)  as Timestamp, 

QTY  as Closing

Resident CUSTOMER

group by DATE;

// --------------------------------------------

OR

// --------------------------------------------

Customer:

load TRANSACTIONDATE,

daystart( TRANSACTIONDATE) as DATE,

        QTY

FROM CUSTOMER;

Fact:

Load DATE, min ( TRANSACTIONDATE)  as Timestamp, 

QTY  as Stock,

'Opening' as Movement

Resident CUSTOMER

group by DATE;

Concatenate (Fact)

Load DATE, max( TRANSACTIONDATE)  as Timestamp, 

QTY  as Stock,

'Closing' as Movement

Resident CUSTOMER

group by DATE;

// --------------------------------------------

Hope this helps,

Erich

[#4]

Hi Erich

Min(tranasction_date) is not working here

it says ODBC read failed.

[CELAMBARASAN]

Hi,

     Can you post your code here?

Celambarasan

[#6]

hi Celambarasan

here is my Code

Inventory:
LOAD  TRANSACTION_ID,
      TRANSACTION_QUANTITY,
      Timestamp#(TRANSACTION_DATE) as MaxTransactionNumber,
      TRANSACTION_DATE,
      daystart(TRANSACTION_DATE) as DATE from Customer;

Fact:
load DATE,
     min(TRANSACTION_DATE) as TimeStamp,
     TRANSACTION_QUANTITY as OpeningStockQty
     Resident Inventory Group by DATE;
    
    
Concatenate(Fact)  
Load DATE,max(TRANSACTION_DATE) as TimeStamp,
     TRANSACTION_QUANTITY as ClosingStockQty
     Resident Inventory Group by DATE;

it shows fact table not found

[#7]

Hi friends

can anyone help me out of this problem?

[CELAMBARASAN]

Hi,

     Try with this

     Inititally your loading from table you have to use Select instead of load.here Other than inventory table uses the already loaded data so it needs load.

Inventory:
SELECT TRANSACTION_ID,
      TRANSACTION_QUANTITY,
      Timestamp#(TRANSACTION_DATE) as MaxTransactionNumber,
      TRANSACTION_DATE,
      daystart(TRANSACTION_DATE) as DATE FROM Customer;

Fact:
load DATE,
     min(TRANSACTION_DATE) as TimeStamp,
     TRANSACTION_QUANTITY as OpeningStockQty
     Resident Inventory Group by DATE;
    
    
Concatenate(Fact)  
Load DATE,max(TRANSACTION_DATE) as TimeStamp,
     TRANSACTION_QUANTITY as ClosingStockQty
     Resident Inventory Group by DATE;