Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
hi gaurav
Can u atlst provide screen shots ?
Screenshot of what ???
1. Application developed
2. Excel files
3. Code I wrote.
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...........
Try this,
Alias F1 as S.no., F2 as Product, F3 as Quantity;
FiscalCalendar:
LOAD * Inline
[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:
LOAD Month,OpeningStock;
CrossTable(Month,OpeningStock,1)
LOAD
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
LOAD Month,PrimarySales;
CrossTable(Month,PrimarySales,1)
LOAD
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
LOAD Month,SecondarySales;
CrossTable(Month,SecondarySales,1)
LOAD
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;
hi gaurav,
the code provided by jagan is correct........jst dont include load statement....
look below given code:
CrossTable
(Month, Opening)
LOAD F1,
Apr,
May,
Jun,
Jul,
Aug,
Sep,
Oct,
Nov,
Dec,
Jan,
Feb,
Mar
FROM
(
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.
hi gaurav
Dont apply any joins..........jst load the cross tables tatz it..........
regards,
vaibhav.
I also renamed "Main" (table name) in the code to "Temp".
but It doesn't work & show error like below:
Thanks & Regards
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:
LOAD * Inline
[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)
LOAD * Inline
[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:
LOAD Month,OpeningStock Resident OpeningStock;
DROP Table OpeningStock;
PrimarySales:
CrossTable(Month,PrimarySales,1)
LOAD * Inline
[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)
LOAD Month,PrimarySales Resident PrimarySales;
DROP Table PrimarySales;
SecondarySales:
CrossTable(Month,SecondarySales,1)
LOAD * Inline
[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)
LOAD Month,SecondarySales Resident SecondarySales;
DROP Table SecondarySales;
Resident_Main:
LOAD Month,(OpeningStock + PrimarySales) - SecondarySales as RemainingStock Resident Main;
Cheers!
Hope it helps you.
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 ??