9 Replies Latest reply: Jun 9, 2015 2:06 AM by ted soesanto RSS

    Format Date

      Hi all,

       

      I have a file qvw connected to an excel file. but when I display the date (date_t), this date_t can not connect with field sales (sales and date_t in same table).

      date_connection.png

      I've made the setting at the beginning of the script.

       

      SET TimeFormat='h:mm:ss TT';

      SET DateFormat='D/M/YYYY';

      SET TimestampFormat='D/M/YYYY h:mm:ss[.fff] TT';

      SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

      SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

       

      How to set 'date_t' to connect with sales?

      hope someone help me.

       

      Best regards,

      Ted

        • Re: Format Date
          Settu Periyasamy

          Hi,

           

          Can you scroll down the ListBox (date_t) and check it.. some times the data appear in the bottom.

           

          or goto ListBox Properties -> Sort -> Put State as Ascending..

            • Re: Format Date

              hi settu,

               

              i have sorting the date_t, but there is not connection with sales. sales is from other file (txt file) and date_t is from excel file. but i put in same table.

              date2.png

              • Re: Format Date
                Settu Periyasamy

                Hi ted,

                 

                Actually, there is no Sales Data for  that field date_t. Please follow the Toni's suggestion.

                 

                for your information, i attached the excel file (I mentioned the yellow color columns for there is no sales data associated with date_t) from Sales Table.

                • Re: Format Date
                  Toni Kautto

                  The first thing I think you should do is to eliminate the synthetic keys that you currently have. This will make your data model a bit more solid and enable better reliability for your aggregations.

                   

                  I noticed that you are currently using QlikView 9. This is no longer a supported versions, so I would like to suggest that you start planning for a migration to QlikView 11.20 SR11.

                   

                  Finally your question about date_t and sales filed not being associated properly. If you put these fields into a table box, and clear the selections, you will see that then relation between the various values. My guess is that you have just concatenated the data, and there by missed to combine data rows. Perhaps joining data between your various sources would give you the relation you are looking for.

                   

                  The sample is way too complex for me to understand exactly what you are doping are what the intention is, so you will have to limit the load and troubleshoot each phase to see where your logic breaks.

                  • Re: Format Date
                    Rob Wunderlich

                    More importantly than formatting the date, first get rid of the duplicate fields that are not matching up between the tables. Remove these fields from the sales table.

                     

                    Month_n

                    monthnum

                    Year_n;

                     

                    Those fields should only be in the TimeLine table. And the link between TimeLine and sales should only be one field, "date".

                     

                    -Rob

                     

                    P.S. You guys really should upgrade from V9.

                  • Re: Format Date
                    Toni Kautto

                    My best guess is that your data is not properly associated. This could happen for many reasons, and a sample would make it a lot easier to analyse.

                     

                    Please attach a sample QVW file and perhaps also a copy of the source XLS file.