Urgent Help

[gaurav2017]

Hi there,

I am having 3 excel files

having fields:

ProductName, Quantity, OpeningStock

ProductName, Quantity, PrimarySales

ProductName, Quantity, SecondarySales

resp.

I have to load these 3 files in qlikview & then calculate a new field called "RemainingStock",

which is a calculated field.

RemainingStock = (OpeningStock + PrimarySales) - SecondarySales

How can it be done.

Regards,

Gaurav Malhotra

[#2]

Hi Gaurav,

1. You just need to load 3 excel files

2. Create a straight table with remaining stock dimension.

View attached example.

Hope it helps

Thanks,

Supriya

[gaurav2017]

Hi Supriya,

I am using Personal Edition, so I can't open qvw you attached. Can you Pls share code.

[gaurav2017]

Also I want to calculate "RemainingStock" at the backend in the script.

[#5]

Hi Gaurav,

LOAD prod,

     quan,

     open as o

     FROM

C:\Users\supriya_thigale\Desktop\Book1.xlsx

(ooxml, embedded labels);

LOAD prod,

     quan,

     prim  as p

FROM

C:\Users\supriya_thigale\Desktop\Book2.xlsx

(ooxml, embedded labels);

LOAD prod,

     quan,

     sec

FROM

C:\Users\supriya_thigale\Desktop\Book3.xlsx

(ooxml, embedded labels);

Then i have created a straight table with remaining stock as dimension.

Thanks

Supriya

[gaurav2017]

What you wrote in the expression field in your straight table ??

or

Did you use "Calculated Dimension" in the Dimension Tab ??

[#7]

I wote

=(OpeningStock + PrimarySales) - SecondarySales

and labelled it as RemainingStock..

It worked very fine

[#8]

You can use expression and calculated dimension both.. Both work fine

[#9]

Hi Gaurav,

Try following code:

temp:

LOAD ProductName,

          Quantity,

          OpeningStock

FROM

C:\Users\Desktop\Book1.xlsx

(ooxml, embedded labels);

INNER JOIN

LOAD ProductName,

          Quantity,

          PrimarySales

FROM

C:\Users\Desktop\Book2.xlsx

(ooxml, embedded labels);

INNER JOIN

LOAD ProductName,

          Quantity,

          SecondorySales

FROM

C:\Users\Desktop\Book3.xlsx

(ooxml, embedded labels);

data:

LOAD *,

         (OpeningStock + PrimarySales) - SecondarySales as RemainingStock

RESIDENT temp;

DROP TABLE temp;

Hope this helps.

BR, Neha

[gaurav2017]

Thanks Supriya & Neharangari. Sorry, I never mentioned that all 3 tables are crosstables. I guess 'Join' don't work in crosstables.