9 Replies Latest reply: Jul 16, 2015 3:28 AM by Henric Cronström RSS

    Issue with ... a lot of thinks in fact synth key / date format / master calendar

    bruno bertels

      Hi community,

       

      I'am new to qlik Sense desktop since few month now and i built this app attached for whom I'm facing Big issues.

       

      Apologize in advance for my approximative english and then for my poor knowledge in Qlik Sense

       

      So i need your help because i'm loosing my head looking for solutions.

       

      First :

      I'am encountering a lot of synthetic keys , And i'm not able to fixed them : my app is built with several excel files witch contain the same fields ( for exemple 'conseiller' and 'semaine' / ( 'Representative' and 'week') and i need both of them. ) :

      Could someone help me in fixing this ?

       

      Second :

      I have the most awfull Date field format it could be ... and my database just change this format to a new one. In a result, after week 27 i'am not able to see the date in graphs and tables.

      From week 1 to 27 my date field looks like that in excel : ( it looks like a string or text format and not as a numeric one)

      22 avr. 2015 18:25

       

      So i needed to floor this field

      date(floor(Timestamp#("Date",'DD MMMM YYYY hh:mm')),'DD/MM/YYYY') as "date du Rdv",

      But since last week the timestamp format has changed for that : (it looks like a numeric format even if they both seem similar)

      15 juil 2015 06:31

       

      So i add en Alt() statement in my formula :

      Alt(Date(Floor(Date#(Date,'DD MMMM YYYY hh:mm')),'DD/MM/YYYY'),Date(Floor(Date(Date,'DD MMMM YYYY hh:mm')),'DD/MM/YYYY'))

       

      My Problem is that i am unable to create a master calendar with that ( see in the app my poor try)

       

      Same question than for first point : could someone have a glance to my app and help me with that.

       

      Thanks a lot in advance

       

      Regards

       

      Bruno !

        • Re: Issue with ... a lot of thinks in fact synth key / date format / master calendar
          Michael Tarallo

          Hi Bruno, to avoid duplicate responses, I will attempt to respond in multiple parts.

           

          Other community members may chime in as well, so please stand by.

           

          Regards,

           

          Mike T

          Qlik

          • Re: Issue with ... a lot of thinks in fact synth key / date format / master calendar
            Michael Tarallo

            Synthetic keys - are technically not an issue when it comes to results, however depending on the size of the data model, IT COULD cause a performance issue in some occurrences. Synthetic keys is how Qlik Sense creates compound or composite keys (using 2 like fields with similiar values to create a unique key) - this is because you have multiple tables with the same column names. If you want to avoid this, you can rename or qualify the other columns, so the fields only associate on the columns you want. Take a look at this video here - our new Smart Data Load capability in Qlik Sense 2.0 can help in situations like this:

             

            Qlik Sense - Using Smart Data Load (video)

             

            Also - if you want to read up more on Synthetic keys - check out this blog post which has many links out to other interesting resources and discussions: Synthetic Keys

             

            I will respond to your second inquiry shortly.

             

            Please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.

             

            Regards,

            Mike Tarallo

            Qlik

            • Re: Issue with ... a lot of thinks in fact synth key / date format / master calendar
              Michael Tarallo

              For #2 - just for reference, inside your APP, the script you entered in the Calendar section, is technically NOT a Master Calendar, the script in your calendar section is actually just deriving new date dimension attributes from your existing "Date" field. The derive and declare syntax is just used to automate the process of defining these dimensions instead of creating them manually in the master items.

               

              If you want to use - or create a true Master Calendar - please check out this video and sample:

               

              Understanding the Master Calendar (video)

               

              Looking at your Date field: - as you have stated, you have a mix of date formats - which makes it difficult to standardize as the expression used will need to accommodate both formats.

               

              I noticed the character length is different - maybe you can use some IF logic to capture the strings character length and if it is 18 (old format) use one date expressions and if it is 19 (new format) use another date expression - to create the single standard expression. There maybe another way to do this. Let me give it a quick try and then we can solicit the help from others if it does not work out.

               

               

               

              Please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.

               

              Regards,

              Mike Tarallo

              Qlik

              • Re: Issue with ... a lot of thinks in fact synth key / date format / master calendar
                Michael Tarallo

                Hi Bruno - you might want to take a look at using these string functions to assist you with the Date field:

                 

                String Functions

                 

                This may take some work, if you can - it might be easier to fix this on the Excel side if you can. The mixed date formats is something that should be fixed on your DB side IMHO.

                 

                jpe or hic - do you have any input on Bruno's #2 issue? - He has a date filed with mixed string formats which is causing an issue.

                 


                Regards,

                 

                Mike T

                Qlik

                  • Re: Issue with ... a lot of thinks in fact synth key / date format / master calendar
                    Henric Cronström

                    First of all, the Synthetic keys: As Mike says, they need not be bad, but in your case they are. You have a synthetic key created from other synthetic keys, and this is a sure sign that your data model needs to be corrected.

                     

                    Secondly, the dates: The ones that are left-aligned are not interpreted as dates. In other words: They are just non-numeric strings to Qlik Sense and will be treated as such. Of course you want them to be correctly interpreted.

                     

                    The Alt() function is indeed the correct approach to fix this, perhaps:

                     

                    Date(Floor(

                    Alt(

                      Timestamp#(Date,'DD MM YYYY hh:mm'),

                      Timestamp#(Date,'DD MMM YYYY hh:mm'),

                      Timestamp#(Date,'DD MMMM YYYY hh:mm')

                      )),

                    'DD/MM/YYYY')

                     

                    The Set statements in the beginning of the script must correspond to your month names.

                    SET MonthNames='janv.;févr.;mars;avr.;mai;juin;juil.;août;sept.;oct.;nov.;déc.';

                    SET LongMonthNames='janvier;février;mars;avril;mai;juin;juillet;août;septembre;octobre;novembre;décembre';

                     

                    In the format code, MMM corresponds to the MonthNames, and MMMM corresponds to the LongMonthNames. In your case, you have a month 'juil' that doesn't correspond to any of the two. So, change MonthNames or LongMonthNames to what you have.

                     

                    HIC

                      • Re: Issue with ... a lot of thinks in fact synth key / date format / master calendar
                        bruno bertels

                        Hi Henric

                         

                        Thanks a lot for your help.

                         

                        I think also the Alt() function is the good approach.

                         

                        I tried this formula in my script and it seems to work well this time :

                        Alt(

                                Date(

                                    Floor(

                                        Date#(Date,'DD MMMM YYYY hh:mm')),'DD/MM/YYYY'),

                                        Date(Floor(Date(Date,'DD MMMM YYYY hh:mm')),'DD/MM/YYYY')

                                )

                                        as "date du Rdv",

                         

                        Look at this sample table :

                         

                        You see date field between week 27 and 28 changes format ( surely string to numéric)

                        but with the alt() function using Floor() Date and Date# i'am able to get the date in this format 'DD/MMM/YYYY'

                        I hope it's always  numeric.

                        So i can create the same with Week , month quarter semester and so on.

                         

                         

                        So, now i can fix the problem and work in the creation of a master calendar depending if it is possible to use a created field in the master calendar script ( "date du Rdv").

                         

                        I 'am changing my app with this solution. Would you like me to post it when  it will done to have a glance ?

                         

                        Bruno

                          • Re: Issue with ... a lot of thinks in fact synth key / date format / master calendar
                            Henric Cronström

                            You still have a problem with your Alt() function. You have the following (I have changed the indentation, but nothing else):

                            Alt(
                            Date(Floor(Date#(Date,'DD MMMM YYYY hh:mm')),'DD/MM/YYYY'),
                            Date(Floor(Date(Date,'DD MMMM YYYY hh:mm')),'DD/MM/YYYY')
                            )
                            as "date du Rdv",

                             

                            First, you miss a hash sign # in your second interpretation alternative.

                             

                            Secondly, I would put the Date(Floor(...)) outside the Alt() function. But that is not important - it will work either way.

                             

                            Thirdly, both interpretation alternatives are identical. Is this what your want? If so, you don't need the Alt() function, but should instead just use:

                            Date(Floor(Date#(Date,'DD MMMM YYYY hh:mm')),'DD/MM/YYYY')
                            as "date du Rdv",

                             

                            HIC