Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi Rahul,
Welcome to QV Community world....
Try this function in your script
monrhname(Datefield) as Yearmonth
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.
Thanks a lot Niramalji!!!
Thank you Jagan, now i'll try this n let u know!!!!
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
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.
Hi
try this
Data:
LOAD
*,
Date(num(Date#([Inv. Date], 'DD MMM YYYY')), 'MMM YYYY') AS MonthYear
FROM ExcelFile;
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...