Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
gaurav2017
Contributor 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

Re: Urgent Help

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
Contributor II

Re: Urgent Help

Hi Supriya,

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

gaurav2017
Contributor II

Re: Urgent Help

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

Not applicable

Re: Urgent Help

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
Contributor II

Re: Urgent Help

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

or

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

Not applicable

Re: Urgent Help

I wote

=(OpeningStock + PrimarySales) - SecondarySales

and labelled it as RemainingStock..

It worked very fine

Not applicable

Re: Urgent Help

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

Not applicable

Re: Urgent Help

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
Contributor II

Re: Urgent Help

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

Community Browser