10 Replies Latest reply: Dec 2, 2011 7:30 PM by Sokkorn Cheav RSS

    Cross Table

    Tracy Crown

      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 Cheav

          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
              Tracy Crown

              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

                • Re: Cross Table
                  Sokkorn Cheav

                  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
                      Tracy Crown

                      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

                        • Re: Cross Table
                          Sokkorn Cheav

                          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
                              Tracy Crown

                              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
                                Tracy Crown

                                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

                      • Re: Cross Table

                        Hi, try with this

                          • Re: Cross Table
                            Tracy Crown

                            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