Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
paulyeo11
Master
Master

Case 881 How to create expression to compute inventory Amount with Latest exchange Rate ?

Hi All

I have below load script working fine :-

// Part 1 Load the inventory raw data for SOURCE indonesia  

INVENTORY:
LOAD
'TI' as SOURCE,
(@54:60T)*(@61:75T) as C_ONHAND_AMT
From $(vRAWPath)$(vFile263)(ansi, fix, no labels, header is 0, record is line);

// Part 2 Load the inventory raw data for SOURCE Singapore 

LOAD left (FileBaseName(), 4) AS TDSS_invc,
'TS' as SOURCE,
(@54:64T)*(@65:75T) as C_ONHAND_AMT,
Date((@286:296T), 'DD-MMM-YYYY') as INVC_DATA_TDSS
FROM $(vRAWPath)$(vFile253) (ansi, fix, no labels, header is 0, record is line);

// Part 3 Load the monthly exchange table

LOAD SOURCE,
Rate,
MonthName(date) as [SalesMonth], //Join on to sales table -> Dont change name
Rate_
FROM
Map_Rate_.xlsx
(ooxml, embedded labels, table is Sheet);

My Table need expression for compute amount base on latest exchange rate for June 15000.

I have Try :- Sum(C_ONHAND_AMT)*Rate , it display Null.

Untitled.png

My question is how to compute 301,887,280 X 15000 ( 2020 June Exchange Rate )

My data model :-

table model.png

Hope some one can advise me.

Paul Yeo

1 Solution

Accepted Solutions
saran7de
Master
Master

Can u try this?

Sum(C_ONHAND_AMT)*FirstSortedValue(Rate,-SalesMonth)

View solution in original post

1 Reply
saran7de
Master
Master

Can u try this?

Sum(C_ONHAND_AMT)*FirstSortedValue(Rate,-SalesMonth)

View solution in original post