Hi, this is my first day on this. I have an query, can we create calculated fields using two or more fields from two different xls sheets?

[rahulhv1]

calculated fields using two or more fields from two different xls sheets? Also, I have a cloumn in which all dates are stored, i wish these data should get converted into uniq months with different columns like  JAN2013  FEB2013  MAR2013 etc.

[#2]

Hi Rahul,

Welcome to QV Community world....

Try this function in your script

monrhname(Datefield) as Yearmonth

[jagan]

Hi Rahul,

For getting calculated fields using two or more fields from two different xls sheets you need to join the data in two sheets and to get month use this in script

Data:

LOAD

*

Date(DateDimensionName, 'MMMYYYY') AS MonthYear

FROM DataSource;

CalculatedFields from two xls:

DataTemp:

LOAD

*

FROM Sheet1;

LEFT JOIN

LOAD

*

FROM Sheet2;

Data:

LOAD

*,

Field1 & Field2  AS NewDimension

RESIDENT DataTemp;

DROP TABLE DataTemp;

Regards,

Jagan.

[rahulhv1]

Thanks a lot Niramalji!!!

[rahulhv1]

Thank you Jagan, now i'll try this n let u know!!!!

[rahulhv1]

This is what i am looking for :

Sheet1 :

Inv. Date           qty        Rate       Amount

01 Jan 2013     10           10         100

01 jan 2013      12           10         120

01 Feb 2013     10           20         200

01 Mar 2013      5           20          100

Output that i want to have

Month Name    Qty   Amount

JAN 2013        22     220

FEB 2013       10     200

MAR 2013       05    100

[jagan]

Hi Rahul,

Try this

Data:

LOAD

*,

Date(Date#([Inv. Date], 'DD MMM YYYY'), 'MMM YYYY') AS MonthYear

FROM ExcelFile;

Now use MonthYear as Dimension and Sum(qty) and Sum(Amount) as expressions.

Hope this helps you.

Regards,

Jagan.

[#8]

Hi

try this

Data:

LOAD

*,

Date(num(Date#([Inv. Date], 'DD MMM YYYY')), 'MMM YYYY') AS MonthYear

FROM ExcelFile;

[MK_QSL]

Dear Rahul,

If you have many dates with different years, try to generate Master Calender in your script using Min and Max Date.

This will give you Complete Set of Dates, Months, Years, Quarters, MonthYear etc.

Try to search Creating Master Calender in QlikView in Youtube...

Hope this help...