Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all
I think the date has created the problem, can someone help to correct my script and explain why the results from QV Cross Table is so different from Excel ?
Thank You
Tracy
Hi, try with this
Hi Tracy,
Maybe I miss your point.
One question is why you need to use CrossTable in your script? Depend on your pivot table, I think just load * from DM$ is enough for your pivot table. Why I suggest you just load *, because your data source (Excel File) is already in your pivot table fomate.
Regards,
Sokkorn Cheav
Dear Sokkorn
I understand that without using cross table, I cannot create both Year & Month date fields for selection.
In addition, I also don't know how to covert those numbers below such as 40209, 20237 ..... into Year and Month.
DM:
Directory;
LOAD
Product,
DM,
FROM Query.xls (biff, embedded labels, table is DM$);
Thank You
Tracy
Date: Thu, 1 Dec 2011 00:04:42 -0500
From: qliktech@sgaur.hosted.jivesoftware.com
To: tracycrown@hotmail.com
QlikCommunity
Re: Cross Table created by Sokkorn Cheav in Development (QlikView Desktop) - View the full discussion
Hi Tracy,
Maybe I miss your point.
One question is why you need to use CrossTable in your script? Depend on your pivot table, I think just load * from DM$ is enough for your pivot table. Why I suggest you just load *, because your data source (Excel File) is already in your pivot table fomate.
Regards,
Sokkorn Cheav
Reply to this message by replying to this email -or- go to the message on QlikCommunity
Start a new discussion in Development (QlikView Desktop) by email or at QlikCommunity
© 1993-2011 QlikTech International AB Copyright & Trademarks | Privacy | Terms of Use | Software EULA
Hi Tracy,
If we change the column header from date format to general or text in excel file then maybe solve the issue.
Regards,
Sokkorn Cheav
Dear Sokkorn
No sure you have tried it before as it does not work after changing date format to text or general in excel, please help.
Thanks
Tracy
Date: Thu, 1 Dec 2011 05:47:55 -0500
From: qliktech@sgaur.hosted.jivesoftware.com
To: tracycrown@hotmail.com
QlikCommunity
Re: Cross Table created by Sokkorn Cheav in Development (QlikView Desktop) - View the full discussion
Hi Tracy,
If we change the column header from date format to general or text in excel file then maybe solve the issue.
Regards,
Sokkorn Cheav
Reply to this message by replying to this email -or- go to the message on QlikCommunity
Start a new discussion in Development (QlikView Desktop) by email or at QlikCommunity
© 1993-2011 QlikTech International AB Copyright & Trademarks | Privacy | Terms of Use | Software EULA
Hi, try with this
Morning Tracy,
How are you doing? Ok now let try with this hard code if you cannot change column lable in excel file.
[tmpOrder]:
CrossTable(Date, Order_Amt, 2)
LOAD Product,
[Order] AS [OrderNo],
[40209],
[40237],
[40268],
[40298],
[40329],
[40359],
[40390],
[40420],
[40451],
[40482],
[40510],
[40538]
FROM
[.\Query.xls]
(biff, embedded labels, table is Order$);
[Order_Data]:
LOAD
Product &'-'& MONTH(NUM(TRIM(Date))) &'-'&YEAR(NUM(TRIM(Date))) AS [%Key],
Product AS [Order_Product],
OrderNo,
Order_Amt,
MONTH(NUM(TRIM(Date))) AS [Order_Month],
YEAR(NUM(TRIM(Date))) AS [Order_Year]
RESIDENT [tmpOrder];
DROP TABLE [tmpOrder];
[tmpDM]:
CrossTable(Date, DM_Data, 2)
LOAD Product,
DM,
[40209],
[40237],
[40268],
[40298],
[40329],
[40359],
[40390],
[40420],
[40451],
[40482],
[40510],
[40538],
F15
FROM [.\Query.xls]
(biff, embedded labels, table is DM$);
[DM_Data]:
LOAD
Product &'-'& MONTH(NUM(TRIM(Date))) &'-'&YEAR(NUM(TRIM(Date))) AS [%Key],
Product AS [DM_Product],
DM,
DM_Data AS [DM_Amt],
//DATE(NUM(TRIM(Date)),'dd-MMM-yyyy') AS [DM_Date],
MONTH(NUM(TRIM(Date))) AS [DM_Month],
YEAR(NUM(TRIM(Date))) AS [DM_Year]
RESIDENT [tmpDM];
DROP TABLE [tmpDM];
Please check in attached file also.
HTH and let me know.
Regards,
Sokkorn Cheav
Dear thefourth (Sir/Madam ?)
I have tried and it works fine, you are good.
Thank you very much
Tracy
Date: Thu, 1 Dec 2011 16:23:33 -0500
From: qliktech@sgaur.hosted.jivesoftware.com
To: tracycrown@hotmail.com
QlikCommunity
Re: Cross Table created by thefourth in Development (QlikView Desktop) - View the full discussion
Hi, try with this
Reply to this message by replying to this email -or- go to the message on QlikCommunity
Start a new discussion in Development (QlikView Desktop) by email or at QlikCommunity
© 1993-2011 QlikTech International AB Copyright & Trademarks | Privacy | Terms of Use | Software EULA
Dear Sokkorn
You have provided me a lot of new ideas, hope to learn more from you in future.
Thank you so much.
Tracy
Dear Sokkorn
You have provided me a lot of useful new ideas, hope to learn more from you in future.
Thank you so much.
Tracy
Date: Thu, 1 Dec 2011 20:39:36 -0500
From: qliktech@sgaur.hosted.jivesoftware.com
To: tracycrown@hotmail.com
QlikCommunity
Re: Cross Table created by Sokkorn Cheav in Development (QlikView Desktop) - View the full discussion
Morning Tracy,
How are you doing? Ok now let try with this hard code if you cannot change column lable in excel file.[tmpOrder]:
CrossTable(Date, Order_Amt, 2)
LOAD Product,
FROM
(biff, embedded labels, table is Order$);
LOAD
Product &'-'& MONTH(NUM(TRIM(Date))) &'-'&YEAR(NUM(TRIM(Date))) AS ,
OrderNo,
Order_Amt,
CrossTable(Date, DM_Data, 2)
LOAD Product,
DM,
F15
(biff, embedded labels, table is DM$);
LOAD
Product &'-'& MONTH(NUM(TRIM(Date))) &'-'&YEAR(NUM(TRIM(Date))) AS ,
DM,
//DATE(NUM(TRIM(Date)),'dd-MMM-yyyy') AS ,
Please check in attached file also.
HTH and let me know.
Regards,
Sokkorn Cheav
Reply to this message by replying to this email -or- go to the message on QlikCommunity
Start a new discussion in Development (QlikView Desktop) by email or at QlikCommunity
© 1993-2011 QlikTech International AB Copyright & Trademarks | Privacy | Terms of Use | Software EULA