Cross Table

[tracycrown]

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

[Sokkorn]

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

[tracycrown]

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

Subject: - Re: Cross Table

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

[Sokkorn]

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

[tracycrown]

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

Subject: - Re: Cross Table

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

[#6 · Accepted solution]

Hi, try with this

[Sokkorn]

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

[tracycrown]

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

Subject: - Re: Cross Table

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

[tracycrown]

Dear Sokkorn

You have provided me a lot of new ideas, hope to learn more from you in future.

Thank you so much.

Tracy

[tracycrown]

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

Subject: - Re: Cross Table

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,

AS ,

,

,

,

,

,

,

,

,

,

,

,

FROM

(biff, embedded labels, table is Order$);

:

LOAD

Product &'-'& MONTH(NUM(TRIM(Date))) &'-'&YEAR(NUM(TRIM(Date))) AS ,

Product AS ,

OrderNo,

Order_Amt,

MONTH(NUM(TRIM(Date))) AS ,

YEAR(NUM(TRIM(Date))) AS

RESIDENT ;

DROP TABLE ;

:

CrossTable(Date, DM_Data, 2)

LOAD Product,

DM,

,

,

,

,

,

,

,

,

,

,

,

,

F15

FROM

(biff, embedded labels, table is DM$);

:

LOAD

Product &'-'& MONTH(NUM(TRIM(Date))) &'-'&YEAR(NUM(TRIM(Date))) AS ,

Product AS ,

DM,

DM_Data AS ,

//DATE(NUM(TRIM(Date)),'dd-MMM-yyyy') AS ,

MONTH(NUM(TRIM(Date))) AS ,

YEAR(NUM(TRIM(Date))) AS

RESIDENT ;

DROP TABLE ;

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