topic Script Problem in QlikView

Script Problem

Sun, 11 Mar 2012 07:44:21 GMT

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

Script Problem

Sun, 11 Mar 2012 07:51:55 GMT

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

Script Problem

erichshiino — Mon, 12 Mar 2012 01:11:28 GMT

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;

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

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

Script Problem

Mon, 12 Mar 2012 05:02:49 GMT

Hi Erich

Min(tranasction_date) is not working here

it says ODBC read failed.

Script Problem

CELAMBARASAN — Mon, 12 Mar 2012 05:09:52 GMT

Hi,

Can you post your code here?

Celambarasan

Script Problem

Mon, 12 Mar 2012 05:20:41 GMT

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

Script Problem

Mon, 12 Mar 2012 05:30:59 GMT

Hi friends

can anyone help me out of this problem?

Script Problem

CELAMBARASAN — Mon, 12 Mar 2012 05:43:37 GMT

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;