12 Replies Latest reply: Oct 30, 2017 9:17 AM by Sunny Talwar RSS

    how to create 'inner join' for the 2 data set

    celine xu

      Hi

      Wondering, how could i do the inner join for 2 dataset?

      after I join the data from 'data Manager'  and then load. I went to 'data load editor'.

      did not find any where i can put


      'inner join' or see the data sets 'joined'


      problem is. there are some empty line in the data set

      when i do pivot table , then the 'Empty' row become one separate line.

      I have same issue if I use the bar chart.


      How could I solve this issue?


      Thank you!

        • Re: how to create 'inner join' for the 2 data set
          Sunny Talwar

          What is your script look like? Can you share the script?

            • Re: how to create 'inner join' for the 2 data set
              celine xu

              Sure:

              Set dataManagerTables = '','Stockholm3store7dayPlu','Pluhierarchy','StoreLocation';

              //This block renames script tables from non generated section which conflict with the names of managed tables

               

               

              For each name in $(dataManagerTables)

                  Let index = 0;

                  Let currentName = name;

                  Let tableNumber = TableNumber(name);

                  Let matches = 0;

                  Do while not IsNull(tableNumber) or (index > 0 and matches > 0)

                      index = index + 1;

                      currentName = name & '-' & index;

                      tableNumber = TableNumber(currentName)

                      matches = Match('$(currentName)', $(dataManagerTables));

                  Loop

                  If index > 0 then

                          Rename Table '$(name)' to '$(currentName)';

                  EndIf;

              Next;

              [Stockholm3store7dayPlu]:

              LOAD [ButiN],

              [KvitD],

              [PluId],

              [Sales],

              [Cost],

              [n_items]

              FROM [lib://AttachedFiles/Stockholm3store7dayPlu.csv]

              (txt, codepage is 28591, embedded labels, delimiter is ',', msq);

               

               

              [Pluhierarchy]:

              LOAD [PluId],

              [ProductName],

              [CategoryId],

              [CategoryName],

              [SUBKATEGORI],

              [SUBKATEGORINAMN],

              [SEGMENT],

              [SEGMENTNAMN],

              [SUBSEGMENT],

              [SUBSEGMENTNAMN],

              [LEVNR],

              [LEVNAMN],

              [VARUMARKE],

              [INNEHALL],

              [SORTENHET],

              [MILJOKODNAMN],

              [ARTIKEL_EJ_I_MASTERDATA]

              FROM [lib://AttachedFiles/SKU and StoreInfo.xlsx]

              (ooxml, embedded labels, table is Pluhierarchy);

               

               

              [StoreLocation]:

              LOAD [ButiN],

              [ButikName],

              [Adress],

              [Postcode],

              [lng],

              [lat],

                  GeoMakePoint(lat, lng) as 'Location'

              FROM [lib://AttachedFiles/SKU and StoreInfo.xlsx]

              (ooxml, embedded labels, table is StoreLocation);

               

               

               

               

               

               

              [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'),

                If (DayNumberOfYear($1) <= DayNumberOfYear(Today()), 1, 0) AS [InYTD] ,

                Year(Today())-Year($1) AS [YearsAgo] ,

                If (DayNumberOfQuarter($1) <= DayNumberOfQuarter(Today()),1,0) AS [InQTD] ,

                4*Year(Today())+Ceil(Month(Today())/3)-4*Year($1)-Ceil(Month($1)/3) AS [QuartersAgo] ,

                Ceil(Month(Today())/3)-Ceil(Month($1)/3) AS [QuarterRelNo] ,

                If(Day($1)<=Day(Today()),1,0) AS [InMTD] ,

                12*Year(Today())+Month(Today())-12*Year($1)-Month($1) AS [MonthsAgo] ,

                Month(Today())-Month($1) AS [MonthRelNo] ,

                If(WeekDay($1)<=WeekDay(Today()),1,0) AS [InWTD] ,

                (WeekStart(Today())-WeekStart($1))/7 AS [WeeksAgo] ,

                Week(Today())-Week($1) AS [WeekRelNo] ;

               

               

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

                • Re: how to create 'inner join' for the 2 data set
                  Sunny Talwar

                  May be this

                   

                  For each name in $(dataManagerTables)

                      Let index = 0;

                      Let currentName = name;

                      Let tableNumber = TableNumber(name);

                      Let matches = 0;

                      Do while not IsNull(tableNumber) or (index > 0 and matches > 0)

                          index = index + 1;

                          currentName = name & '-' & index;

                          tableNumber = TableNumber(currentName)

                          matches = Match('$(currentName)', $(dataManagerTables));

                      Loop

                      If index > 0 then

                              Rename Table '$(name)' to '$(currentName)';

                      EndIf;

                  Next;

                  [Stockholm3store7dayPlu]:

                  LOAD [ButiN],

                  [KvitD],

                  [PluId],

                  [Sales],

                  [Cost],

                  [n_items]

                  FROM [lib://AttachedFiles/Stockholm3store7dayPlu.csv]

                  (txt, codepage is 28591, embedded labels, delimiter is ',', msq);

                   

                   

                  Inner Join ([Stockholm3store7dayPlu])

                  LOAD [PluId],

                  [ProductName],

                  [CategoryId],

                  [CategoryName],

                  [SUBKATEGORI],

                  [SUBKATEGORINAMN],

                  [SEGMENT],

                  [SEGMENTNAMN],

                  [SUBSEGMENT],

                  [SUBSEGMENTNAMN],

                  [LEVNR],

                  [LEVNAMN],

                  [VARUMARKE],

                  [INNEHALL],

                  [SORTENHET],

                  [MILJOKODNAMN],

                  [ARTIKEL_EJ_I_MASTERDATA]

                  FROM [lib://AttachedFiles/SKU and StoreInfo.xlsx]

                  (ooxml, embedded labels, table is Pluhierarchy);

                   

                   

                  Inner Join ([Stockholm3store7dayPlu])

                  LOAD [ButiN],

                  [ButikName],

                  [Adress],

                  [Postcode],

                  [lng],

                  [lat],

                      GeoMakePoint(lat, lng) as 'Location'

                  FROM [lib://AttachedFiles/SKU and StoreInfo.xlsx]

                  (ooxml, embedded labels, table is StoreLocation);

                   

                   

                   

                   

                   

                   

                  [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'),

                    If (DayNumberOfYear($1) <= DayNumberOfYear(Today()), 1, 0) AS [InYTD] ,

                    Year(Today())-Year($1) AS [YearsAgo] ,

                    If (DayNumberOfQuarter($1) <= DayNumberOfQuarter(Today()),1,0) AS [InQTD] ,

                    4*Year(Today())+Ceil(Month(Today())/3)-4*Year($1)-Ceil(Month($1)/3) AS [QuartersAgo] ,

                    Ceil(Month(Today())/3)-Ceil(Month($1)/3) AS [QuarterRelNo] ,

                    If(Day($1)<=Day(Today()),1,0) AS [InMTD] ,

                    12*Year(Today())+Month(Today())-12*Year($1)-Month($1) AS [MonthsAgo] ,

                    Month(Today())-Month($1) AS [MonthRelNo] ,

                    If(WeekDay($1)<=WeekDay(Today()),1,0) AS [InWTD] ,

                    (WeekStart(Today())-WeekStart($1))/7 AS [WeeksAgo] ,

                    Week(Today())-Week($1) AS [WeekRelNo] ;

                   

                   

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

                    • Re: how to create 'inner join' for the 2 data set
                      celine xu

                      Thank you Sunny,

                       

                      but seems it does not work

                      Screen Shot 2017-10-30 at 13.44.54.pngou

                      any suggestion?

                      I add the line you add to the place

                      • Re: how to create 'inner join' for the 2 data set
                        Sunny Talwar

                        Try with inner keep may be


                        [Pluhierarchy]:

                        Inner Keep ([Stockholm3store7dayPlu])

                        LOAD [PluId],

                        [ProductName],

                        [CategoryId],

                        [CategoryName],

                        [SUBKATEGORI],

                        [SUBKATEGORINAMN],

                        [SEGMENT],

                        [SEGMENTNAMN],

                        [SUBSEGMENT],

                        [SUBSEGMENTNAMN],

                        [LEVNR],

                        [LEVNAMN],

                        [VARUMARKE],

                        [INNEHALL],

                        [SORTENHET],

                        [MILJOKODNAMN],

                        [ARTIKEL_EJ_I_MASTERDATA]

                        FROM [lib://AttachedFiles/SKU and StoreInfo.xlsx]

                        (ooxml, embedded labels, table is Pluhierarchy);

                         

                        [StoreLocation]:

                        Inner Keep ([Stockholm3store7dayPlu])

                        LOAD [ButiN],

                        [ButikName],

                        [Adress],

                        [Postcode],

                        [lng],

                        [lat],

                            GeoMakePoint(lat, lng) as 'Location'

                        FROM [lib://AttachedFiles/SKU and StoreInfo.xlsx]

                        (ooxml, embedded labels, table is StoreLocation);

                    • Re: how to create 'inner join' for the 2 data set
                      celine xu

                      And also see your comment regarding chose the top 10 product for bar chart but is that the same trick for Qliq sense?

                       

                       

                      Same dataset,

                      want to show top 10 'ProductName', Ranking by 'sum([n_items])'

                      Thank you. let me know if you prefer will ask the it separately 

                    • Re: how to create 'inner join' for the 2 data set
                      celine xu

                      Sorry. I tried both. but does not work.