13 Replies Latest reply: Jan 16, 2017 5:37 AM by denis Brennan RSS

    New to Qliksense - Script

    Anil Babu Samineni

      Hello Friends,

       

      I just start fun with qliksense. Can anyone tell me I just download 3.1 SR3 and i've seen default application. Every script ended with below one

       

      [autoCalendar]:

        DECLARE FIELD DEFINITION Tagged ('$date')

      FIELDS

        Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),

        Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged

       

       

      ('$quarter'),

        Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged

       

       

      ('$axis', '$yearquarter'),

        Month($1) AS [Month] Tagged ('$month'),

        Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth'),

        Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber'),

        Date(Floor($1)) AS [Date] Tagged ('$date');

       

       

      DERIVE FIELDS FROM FIELDS "Month" USING [autoCalendar] ;

       

      What is the usage of above one, Is this act as Calendar in qlikview / What? Can you describe more

       

      And if some one have any qlik sense content for guide please share that content with me

        • Re: New to Qliksense - Script
          Chanty 4u

          Hi anil,

          AFAIK

          This code about master calender and it is auto generated script  when you have a single date field in your data

          automatically it will generate the code.

           

           

          HTH

          Chanty

          • Re: New to Qliksense - Script
            Robert Hutchings

            Hi Anil

             

            You can create automatic script using Data Manager. This creates an automatic calendar for every date using the derived date feature and auto calendar

             

            If you use Data load editor you can still used derived date. I sometimes use this now with a canonical date as you can do time aware charts. And it automatically creates the calendar (one date for every day between start a dn finish) for the canonical date

             

            Qlik Sense – Date & Time

             

            https://community.qlik.com/blogs/qlikviewdesignblog/2014/02/17/canonical-date?_ga=1.267491272.717680125.1474607938#start…

             

            What’s new in Qlik Sense 3.0 - Time-aware Charts

             

             

             

             

             

            temp3.JPG

              • Re: New to Qliksense - Script
                denis Brennan

                Robert,

                 

                What if I have multiple fact tables each with dates and I want to have a canonical date so I can filter on it and see values from each table for that date?

                 

                Here;s what the data manager loaded:

                 

                 

                 

                [Sites]:

                LOAD [Site_ID],

                  [Sitename],

                  [Mid],

                  [Add_1],

                  [Add_2],

                  [Postcode],

                  [Latitude],

                  [Longitude],

                  [F9],

                  [F10],

                  [F11],

                  [F12],

                  [F13]

                FROM [lib://qlikid_djbrennan/Anon Sites.xlsx]

                (ooxml, embedded labels, table is Sites);

                 

                 

                [Sheet1_c872b52b-b735-95f5-5de3-e6d41176]:

                LOAD

                  [Site_ID],

                  [Merchant ID],

                  [Site],

                  Date([St_Date] ,'DD/MM/YYYY') AS [StDate],

                  [Card_Type],

                  [Auth],

                  [Turnover],

                  [Transactions],

                  [Rate],

                  [MSC Fee]

                FROM [lib://qlikid_djbrennan/Ex_Cards.xlsx]

                (ooxml, embedded labels, table is Sheet1);

                 

                 

                [Sheet1_23a70457-064d-9065-8a8b-91f5ea5e]:

                LOAD

                  [MPANID],

                  Date([Date] ,'DD/MM/YYYY') AS [ElecDate],

                  [TimeSlot],

                  [Value],

                  [DayNight],

                  [WDWE]

                FROM [lib://qlikid_djbrennan/Ex_Elec.xlsx]

                (ooxml, embedded labels, table is Sheet1);

                 

                 

                [Sheet1_be0ddd76-8edf-9f96-e671-c66a1fd1]:

                LOAD

                  [Site_ID],

                  [Telephone_No],

                  Date([Date] ,'DD/MM/YYYY') AS [FixedDate],

                  [Time],

                  [Dialled_No],

                  [Description] AS [Sheet1-2.Description],

                  [Dur_secs],

                  [Cost (£)],

                  [Call_Type],

                  [PPM],

                  [Focus_Contract],

                  [Opt_PPM],

                  [Opt_Cost],

                  [Saving]

                FROM [lib://qlikid_djbrennan/Ex_Fixed.xlsx]

                (ooxml, embedded labels, table is Sheet1);

                 

                 

                [Sheet1_74ec4ccd-6154-d91d-87e1-d3d9dc5c]:

                LOAD

                  [Invoice],

                  Date([Invoice Date] ,'DD/MM/YYYY') AS [MobDate],

                  [Account number],

                  [Mob_Num],

                  [Description] AS [Sheet1-3.Description],

                  [Nature],

                  [Net_Charge],

                  [Gross _Charge]

                FROM [lib://qlikid_djbrennan/Ex_Mobile.xlsx]

                (ooxml, embedded labels, table is Sheet1);

                 

                 

                [Curr_el_contracts]:

                LOAD [MPANID],

                  [Company],

                  [Site_ID],

                  [Supply],

                  [ASC/KVA],

                  [Top line],

                  [Contract Start],

                  [Contract End],

                  [StCharge],

                  [DayRate],

                  [NightRate],

                  [DUOSCh],

                  [FIT]

                FROM [lib://qlikid_djbrennan/Anon Site_elec_Dim.xlsx]

                (ooxml, embedded labels, table is Curr_el_contracts);

                 

                 

                [Mob_users]:

                LOAD [Mob_Num],

                  [User],

                  [Site_ID],

                  [E],

                  [F],

                  [G],

                  [H],

                  [I],

                  [J]

                FROM [lib://qlikid_djbrennan/Anon Mob_Users.xlsx]

                (ooxml, embedded labels, table is Mob_users);

                 

                 

                RENAME TABLE [Sheet1_c872b52b-b735-95f5-5de3-e6d41176] TO [Cards];

                RENAME TABLE [Sheet1_23a70457-064d-9065-8a8b-91f5ea5e] TO [Elec];

                RENAME TABLE [Sheet1_be0ddd76-8edf-9f96-e671-c66a1fd1] TO [Fixed];

                RENAME TABLE [Sheet1_74ec4ccd-6154-d91d-87e1-d3d9dc5c] TO [Mobile];

                 

                 

                 

                 

                [autoCalendar]:

                  DECLARE FIELD DEFINITION Tagged ('$date')

                FIELDS

                  Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),

                  Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter', '$cyclic'),

                  Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),

                  Dual('Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),

                  Month($1) AS [Month] Tagged ('$month', '$cyclic'),

                  Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),

                  Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),

                  Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'),

                  Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'),

                  Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified');

                 

                 

                DERIVE FIELDS FROM FIELDS [StDate], [ElecDate], [FixedDate], [MobDate], [Contract Start], [Contract End] USING [autoCalendar] ;

                  • Re: New to Qliksense - Script
                    Robert Hutchings

                    Hi denis

                     

                    What you have got is one calendar for every date. So you can expand the date(s) and filter as you require. But if you use the FixedDate.Month filter you will like get the wrong totals if you want say  MobDate

                     

                    The advantage with the canonical date is you set up one calendar only. And use this one calendar (Day / Month / Year etc) for all dates

                     

                    But you must create a date bridge between these date

                     

                    https://community.qlik.com/blogs/qlikviewdesignblog/2014/02/17/canonical-date?_ga=1.267491272.717680125.1474607938#start…

                      • Re: New to Qliksense - Script
                        denis Brennan

                        Robert,

                         

                        Many thanks. I'm struggling with the script. I adapted this from Henric's post on canonical dates but I know I'm missing something.

                         

                        DateBridge:

                            Load SiteID, Applymap(‘SiteID2ElecDate’,SiteID,Null()) as CanonicalDate, ‘Elec’ as DateType

                                  Resident HH;

                            Load SiteID, Applymap(‘SiteID2CardDate',OrderID,Null()) as CanonicalDate, ‘Card’ as DateType

                                  Resident Cards;

                            Load SiteID, FixedDate as CanonicalDate, ‘Fixed’ as DateType

                                  Resident Fixed;

                            Load SiteID, MobDate as CanonicalDate, ‘Mob’ as DateType

                                  Resident Mobiles;


                        I also attach my QVF ( a small set of sample data. Can you point me in the right direction?



                          • Re: New to Qliksense - Script
                            Robert Hutchings

                            hi

                             

                            Very quickly looking at it. I don't think Canonical date is right for this (as you don't have one table with the right level of detail or grain)

                             

                            First you must find a table with a grain fine enough; a table where each record only has one value of each date type associated.

                             

                            Try instead (by using Data load editor)

                             

                            Concatenating (stacking one on top of the other) the three data tables (Fixed data / elecData / Card Data)

                             

                            after changing the three date names to Date (or CombinedDate etc)

                             

                            eg FixedDate as Date

                            ElecDate as Date

                            StDate as Date

                             

                            then only have a derived date as Date

                            eg

                            DERIVE FIELDS FROM FIELDS [Date] USING [autoCalendar] ;

                              • Re: New to Qliksense - Script
                                denis Brennan

                                I very much appreciate this help. I just wanted to let you know that I won't be able to try it until tomorrow morning and will let you know how it goes.

                                 

                                Best wishes

                                Denis

                                 

                                Get Outlook for iOS<https://aka.ms/o0ukef>

                                  • Re: New to Qliksense - Script
                                    Robert Hutchings

                                    add this after the auto-generated section.

                                     

                                    It should work. Just filter by Day / Month / Year etc

                                     

                                    But Data Manager is best for simple loads.Doing what you are doing. Its best to do everything with script using data Load editor. But for now this should work

                                     

                                    //================================================================

                                    DATANew:

                                    Load

                                    * ,

                                    Fixed.Date AS DateC

                                    RESIDENT Fixed_data ;

                                     

                                     

                                    Concatenate (DATANew)

                                    Load

                                    * ,

                                    ElecDate AS DateC

                                    RESIDENT Elec_data ;

                                     

                                    Concatenate (DATANew)

                                    Load

                                    * ,

                                    St_Date AS DateC

                                    RESIDENT Card_Data ;

                                     

                                    drop table Elec_data , Fixed_data , Card_Data ;

                                     

                                     

                                    Calendar:

                                    load

                                    DateC ,

                                    Year (DateC) AS Year ,

                                    Month (DateC) AS Month ,

                                    Day(DateC) AS Day

                                    resident DATANew ;