Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
gaurav2017
Creator II
Creator II

Urgent Help

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

29 Replies
Not applicable

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
Creator II
Creator II
Author

Hi Supriya,

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

gaurav2017
Creator II
Creator II
Author

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

Not applicable

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
Creator II
Creator II
Author

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

or

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

Not applicable

I wote

=(OpeningStock + PrimarySales) - SecondarySales

and labelled it as RemainingStock..

It worked very fine

Not applicable

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

Not applicable

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
Creator II
Creator II
Author

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