topic Re: Get data from two table in QlikView

Get data from two table

Tue, 07 May 2013 01:52:10 GMT

Hi everyone,

I have two table as following:

//Stock Balance Table

Directory;

Stock_Balance:

LOAD ValA,

Material,

[From Date],

[Opening Value],

[Closing Value],

(([Opening Value]+ [Closing Value])/2) as _average

FROM Data_Source\*_PPCement_StockBal.xlsx (ooxml, embedded labels, table is Sheet1);

// Cost of Goods Sold Table

Directory;

Cost_of_Goods_Sold:

LOAD [Year/month],

[Profit Ctr],

Material,

[Amount in local cur.]

FROM Data_Source\GL\*_PPCement_GL.xlsx (ooxml, embedded labels, table is Sheet1);

I want to calculate [Amount in local cur.]/_average

how to do it?

Best regards,

Ratana

Re: Get data from two table

Sokkorn — Tue, 07 May 2013 02:29:51 GMT

Hi Ratana,

Not sure this script is working for you or not

//Stock Balance Table
Directory;
Stock_Balance:
LOAD ValA,
    Material,
    [From Date],
    Material &'/'& Text(Year([From Date]) &'/'& Month([From Date]))     AS [_Key],
    [Opening Value],
    [Closing Value],
    (([Opening Value]+ [Closing Value])/2) as _average
    
FROM Data_Source\*_PPCement_StockBal.xlsx (ooxml, embedded labels, table is Sheet1);
[AVG]:
Mapping Load
     [_Key],
     [_average]
Resident Stock_Balance;
// Cost of Goods Sold Table
Directory;
Cost_of_Goods_Sold:
LOAD [Year/month],
    [Profit Ctr],
    Material,
    [Amount in local cur.],
     ApplyMap('AVG',Material &'/'&[Year/month],1)     AS [Avg],
     [Amount in local cur.]/ApplyMap('AVG',Material &'/'&[Year/month],1) AS [YourValue]
FROM Data_Source\GL\*_PPCement_GL.xlsx (ooxml, embedded labels, table is Sheet1);

Assumption: [Year/month] have value in format 2013/05

Regards,

Sokkorn

Re: Get data from two table

sujeetsingh — Tue, 07 May 2013 04:19:17 GMT

Ratan,

you can easily do it by using joins on common id or field.

Re: Get data from two table

Tue, 07 May 2013 04:28:35 GMT

try this

Stock_Balance:

LOAD ValA,

Material,

[From Date],

[Opening Value],

[Closing Value],

(([Opening Value]+ [Closing Value])/2) as _average

FROM Data_Source\*_PPCement_StockBal.xlsx (ooxml, embedded labels, table is Sheet1);

join

LOAD [Year/month],

[Profit Ctr],

Material,

[Amount in local cur.]

FROM Data_Source\GL\*_PPCement_GL.xlsx (ooxml, embedded labels, table is Sheet1);

LOAD *, [Amount in local cur.]/_average as Result

Resident Stock_Balance;

Re: Get data from two table

er_mohit — Tue, 07 May 2013 05:31:21 GMT

Directory;

Stock_Balance:

LOAD ValA,

Material,

[From Date],

[Opening Value],

[Closing Value],

rowno() as Count,

(([Opening Value]+ [Closing Value])/2) as _average

FROM Data_Source\*_PPCement_StockBal.xlsx (ooxml, embedded labels, table is Sheet1);

join

Directory;

Cost_of_Goods_Sold:

LOAD [Year/month],

[Profit Ctr],

Material,

rowno() as Count,

[Amount in local cur.]

FROM Data_Source\GL\*_PPCement_GL.xlsx (ooxml, embedded labels, table is Sheet1);

New_Stock_Balance :

LOAD *, [Amount in local cur.]/_average as Result

Resident Stock_Balance;

drop table Stock_Balance;