topic Re: Cross Table in QlikView

Cross Table

tracycrown — Thu, 01 Dec 2011 03:23:04 GMT

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

Cross Table

Sokkorn — Thu, 01 Dec 2011 05:04:28 GMT

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

Re: Cross Table

tracycrown — Thu, 01 Dec 2011 08:03:20 GMT

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.

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

Re: Cross Table

Sokkorn — Thu, 01 Dec 2011 10:47:39 GMT

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

Re: Cross Table

tracycrown — Thu, 01 Dec 2011 17:08:25 GMT

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

Re: Cross Table

Thu, 01 Dec 2011 21:23:04 GMT

Hi, try with this

Re: Cross Table

Sokkorn — Fri, 02 Dec 2011 01:39:23 GMT

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

Re: Cross Table

tracycrown — Fri, 02 Dec 2011 02:35:06 GMT

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

Re: Cross Table

tracycrown — Fri, 02 Dec 2011 23:37:12 GMT

Dear Sokkorn

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

Thank you so much.

Tracy

Re: Cross Table

tracycrown — Fri, 02 Dec 2011 23:39:42 GMT

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

Re: Cross Table

Sokkorn — Sat, 03 Dec 2011 00:30:00 GMT

Hi Tracy,

We are the world not alone. Learn and share together . You are welcome.

Regards,

Sokkorn Cheav