Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Save $300 - Register for QlikWorld (formerly Qonnections) by January 31st: Learn More
Highlighted
yangcelinexu
Contributor

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

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!

Tags (2)
1 Solution

Accepted Solutions

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

I am not really sure what might be happening, what I gave you was the syntax for Inner Join and Inner Keep. May be someone else can offer better help here

View solution in original post

12 Replies

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

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

yangcelinexu
Contributor

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

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] ;

yangcelinexu
Contributor

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

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

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

Not sure I understand?

yangcelinexu
Contributor

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

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

yangcelinexu
Contributor

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

Sorry for confusing.

I am trying to create a bar chart and it has more than 2000 product. But I want to only filted top 10 to show.

was searching answer in the community, came across one answer you provide to the other having similar issue. but using Qlik view.

So i mentioned it. 

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

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);

yangcelinexu
Contributor

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

Sorry. I tried both. but does not work.