29 Replies Latest reply: Dec 22, 2012 6:30 AM by Gaurav Malhotra

# 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

• ###### 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

• ###### Re: Urgent Help

Hi Supriya,

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

• ###### Re: Urgent Help

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

• ###### Re: Urgent Help

Hi Gaurav,

quan,

open as o

FROM

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

(ooxml, embedded labels);

quan,

prim  as p

FROM

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

(ooxml, embedded labels);

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

• ###### Re: Urgent Help

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

or

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

• ###### Re: Urgent Help

I wote

=(OpeningStock + PrimarySales) - SecondarySales

and labelled it as RemainingStock..

It worked very fine

• ###### Re: Urgent Help

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

• ###### Re: Urgent Help

Hi Gaurav,

Try following code:

temp:

Quantity,

OpeningStock

FROM

C:\Users\Desktop\Book1.xlsx

(ooxml, embedded labels);

INNER JOIN

Quantity,

PrimarySales

FROM

C:\Users\Desktop\Book2.xlsx

(ooxml, embedded labels);

INNER JOIN

Quantity,

SecondorySales

FROM

C:\Users\Desktop\Book3.xlsx

(ooxml, embedded labels);

data:

(OpeningStock + PrimarySales) - SecondarySales as RemainingStock

RESIDENT temp;

DROP TABLE temp;

Hope this helps.

BR, Neha

• ###### Re: Urgent Help

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

• ###### Re: Urgent Help

hi gaurav

Can u atlst provide screen shots ?

• ###### Re: Urgent Help

Screenshot of what ???

1. Application developed

2. Excel files

3. Code I wrote.

• ###### Re: Urgent Help

since u have personal edition

provide screen shots for code u have or jst attach the excel file from whr u r getting data for all...........

• ###### Re: Urgent Help

Try this,

Alias F1 as S.no., F2 as Product, F3 as Quantity;

FiscalCalendar:

[Month, Quarter

Jan, Q4

Feb, Q4

Mar, Q4

Apr, Q1

May, Q1

Jun, Q1

Jul, Q2

Aug, Q2

Sep, Q2

Oct, Q3

Nov, Q3

Dec, Q3];

Main:

CrossTable(Month,OpeningStock,1)

F1,

Apr,

May,

Jun,

Jul,

Aug,

Sep,

Oct,

Nov,

Dec,

Jan,

Feb,

Mar

FROM

(ooxml, embedded labels, header is 3 lines, table is RJ, filters(

Remove(Row, Pos(Top, 33)),

Remove(Row, Pos(Top, 32)),

Remove(Row, Pos(Top, 28)),

Remove(Row, Pos(Top, 24)),

Remove(Row, Pos(Top, 20)),

Remove(Row, Pos(Top, 16)),

Remove(Row, Pos(Top, 11)),

Remove(Row, Pos(Top, 6)),

Remove(Col, Pos(Top, 20)),

Remove(Col, Pos(Top, 19)),

Remove(Col, Pos(Top, 18)),

Remove(Col, Pos(Top, 17)),

Remove(Col, Pos(Top, 1))

));

Left Join

CrossTable(Month,PrimarySales,1)

F2,

Apr,

May,

Jun,

Jul,

Aug,

Sep,

Oct,

Nov,

Dec,

Jan,

Feb,

Mar

FROM

(ooxml, embedded labels, header is 3 lines, table is RJ, filters(

Remove(Row, Pos(Top, 33)),

Remove(Row, Pos(Top, 32)),

Remove(Row, Pos(Top, 28)),

Remove(Row, Pos(Top, 24)),

Remove(Row, Pos(Top, 20)),

Remove(Row, Pos(Top, 16)),

Remove(Row, Pos(Top, 11)),

Remove(Row, Pos(Top, 6)),

Remove(Col, Pos(Top, 20)),

Remove(Col, Pos(Top, 19)),

Remove(Col, Pos(Top, 18)),

Remove(Col, Pos(Top, 17)),

Remove(Col, Pos(Top, 1))

));

Left Join

CrossTable(Month,SecondarySales,1)

F3,

Apr,

May,

Jun,

Jul,

Aug,

Sep,

Oct,

Nov,

Dec,

Jan,

Feb,

Mar

FROM

(ooxml, embedded labels, header is 3 lines, table is [RJ (Secondary)], filters(

Remove(Row, Pos(Top, 33)),

Remove(Row, Pos(Top, 32)),

Remove(Row, Pos(Top, 28)),

Remove(Row, Pos(Top, 24)),

Remove(Row, Pos(Top, 20)),

Remove(Row, Pos(Top, 16)),

Remove(Row, Pos(Top, 11)),

Remove(Row, Pos(Top, 6)),

Remove(Col, Pos(Top, 21)),

Remove(Col, Pos(Top, 20)),

Remove(Col, Pos(Top, 19)),

Remove(Col, Pos(Top, 18)),

Remove(Col, Pos(Top, 17)),

Remove(Col, Pos(Top, 1))

));

Res_Temp:

LOAD Month,(OpeningStock + PrimarySales) - SecondarySales as RemainingStock  Resident Temp;

• ###### Re: Urgent Help

hi gaurav,

the code provided by jagan is correct........jst dont include load statement....

look below given code:

CrossTable

(Month, Opening)

Apr,

May,

Jun,

Jul,

Aug,

Sep,

Oct,

Nov,

Dec,

Jan,

Feb,

Mar

FROM

[D:\QlikView\APPLICATIONS\RND\Rajasthan Opening Stock.xlsx]

(
ooxml, embedded labels, header is 3 lines, table is RJ, filters(

Remove(Col, Pos(Top, 1)),

Remove(Col, Pos(Top, 1)),

Remove(Col, Pos(Top, 1)),

Remove(Row, Pos(Top, 6)),

Remove(Row, Pos(Top, 10)),

Remove(Row, Pos(Top, 14)),

Remove(Row, Pos(Top, 17)),

Remove(Row, Pos(Top, 20)),

Remove(Row, Pos(Top, 23)),

Remove(Row, Pos(Top, 26)),

Remove(Row, Pos(Top, 26)),

Remove(Col, Pos(Top, 15)),

Remove(Col, Pos(Top, 14)),

Remove(Col, Pos(Top, 15)),

Remove(Col, Pos(Top, 14))

));

i hope u can undrstand how to load excel wit help of cross table as given above.

• ###### Re: Urgent Help

hi gaurav

Dont apply any joins..........jst load the cross tables tatz it..........

regards,

vaibhav.

• ###### Re: Urgent Help

I also renamed "Main" (table name) in the code to "Temp".

but It doesn't work & show error like below:

Thanks & Regards

• ###### Re: Urgent Help

Gaurav,

I want to expalin you few points.

1. We are trying to develop a expression in script i.e. (OpeningStock + PrimarySales) - SecondarySales . Here we are calculating the expression on fields which are not there in in single table. Each field is coming from different tables. So we need to make single table to calculate this expression.

2. To make a single table we need to use Joins concept. If we don't use joins the qvw loads the three different tables seperately and links the tables with help of common field key.

So what i did here is first i'll load cross table of OpeningStock and in resident table(i.e. Main table) i'll hold the month and values of opening stock. Now i'll load crosstable of PrimarySales and i'll join the resident table of primarysales with Main table. Say thing for SecondarySales table.

I forgot to tell you i'm also droping the tables of OpeningStock, PrimarySales, SecondarySales. B'coz we have this fields in main table. I tried with sample inline data please check for your reference.

Alias F1 as S.no., F2 as Product, F3 as Quantity;

FiscalCalendar:

[Month, Quarter

Jan, Q4

Feb, Q4

Mar, Q4

Apr, Q1

May, Q1

Jun, Q1

Jul, Q2

Aug, Q2

Sep, Q2

Oct, Q3

Nov, Q3

Dec, Q3];

OpeningStock:

CrossTable(Month,OpeningStock,1)

[F1,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec,Jan,Feb,Mar

100,200,300,400,500,600,100,200,300,400,500,600,1000];

Main:

DROP Table OpeningStock;

PrimarySales:

CrossTable(Month,PrimarySales,1)

[F1,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec,Jan,Feb,Mar

100,200,300,400,500,600,100,200,300,400,500,600,1000];

Left Join (Main)

DROP Table PrimarySales;

SecondarySales:

CrossTable(Month,SecondarySales,1)

[F1,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec,Jan,Feb,Mar

10,20,30,40,50,60,10,20,30,40,50,60,10];

Left Join (Main)

DROP Table SecondarySales;

Resident_Main:

LOAD Month,(OpeningStock + PrimarySales) - SecondarySales as RemainingStock  Resident Main;

Cheers!

Hope it helps you.

• ###### Re: Urgent Help

I am very thankful to all of you for replying to my post. Thanks a ton.

Every reply is meaningful to me.

It is stucked at the reload. What to do now ???

Should I wait or abort ??

• ###### Re: Urgent Help

According to logic it should work. What is the wrong? What is the size of your data? If possible attach your script code or file or along with data.

• ###### Re: Urgent Help

Thanks Jagan, It's done with the reload

but unsurprisingly data is not as expected.

Take a look at my code now attached below.

• ###### Re: Urgent Help

Ok. What is your data expection?

Can you reduce your data and develop a sample application. Send your sample application along with data file. And, also let me know what is the output you need from your sample data.

• ###### Re: Urgent Help

I hope it helps.

These are my expectations.

Output:

I should be able to see the remainig stock because it is most important kPI at this time.

• ###### Re: Urgent Help

If that is in the case, why can't you write your 3rd expression as (OpeningStock + PrimarySales) - SecondarySales in straight table itself.

• ###### Re: Urgent Help

Dear,

I had done it & showed to my boss but I want to explore more. It was done before i posted it on community.

• ###### Re: Urgent Help

Why can't you attach sample file? From my case i tried with sample data it is working perfectly. If you attach sample it helps me and other to solve your problem easily.

• ###### Re: Urgent Help

My dear, all sample files needed are attached within the discussion (Look at those Excel files).

• ###### Re: Urgent Help

It takes 05 min & 12 sec to reload whole data. It pisses me off.

• ###### Re: Urgent Help

It was done. Thanks to all for help.:)