Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
Not applicable

Handling joints and cartesian products

Hi there,

A new question following my previous one : Handling dates and cross table

Now that I have my table set up properly, I'd like to go a little further.

I'd like to associate data from the table tmpData2 with its respective market, at a given date. Thus, I would like to build a table that would look like :

(Let's call this table TOTO)

Market

BU

Market_BU

Dates

Market_BU_Dates

Market_Dates

For one market there are 4 and exactly 4 (always the same) possible BUs.

Some data like the data contained in tmpData (so CS_R+B) is related to a Market in general, some is related to a subdivision Market_BU.

I get the couples Market_BU from an excel file that I set up manually.

But I'd like to "multiply" it with the Dates I got from my previous table tmpData so that I can create unique entries Market_BU_Dates to be associated with the relevant value for CS_R+B.

I tried to write it this way but obviously it does not work the way it should :

tmpData:

CrossTable(MonthNumb,[CS_R+B])

LOAD  Mkt_BU,

     [40179],

     [40210],

     [40238],

     [40269],

     [40299],

     [40330],

     [40360],

     [40391],

     [40422],

     [40452],

     [40483],

     [40513],

     [40544],

     [40575],

     [40603],

     [40634],

     [40664],

     [40695],

     [40725],

     [40756],

     [40787],

     [40817],

     [40848],

     [40878],

     [40909],

     [40940],

     [40969],

     [41000],

     [41030],

     [41061],

     [41091],

     [41122],

     [41153],

     [41183],

     [41214],

     [41244],

     [41275],

     [41306],

     [41334],

     [41365],

     [41395],

     [41426],

     [41456],

     [41487],

     [41518],

     [41548],

     [41579],

     [41609],

     [41640],

     [41671],

     [41699],

     [41730],

     [41760],

     [41791],

     [41821],

     [41852],

     [41883],

     [41913],

     [41944],

     [41974],

     [42005],

     [42036],

     [42064],

     [42095],

     [42125],

     [42156],

     [42186],

     [42217],

     [42248],

     [42278],

     [42309],

     [42339]

    

FROM

blablabla.xlsx

(ooxml, embedded labels, table is tab1);

tmpData2:

  LOAD

  Date(Num#(MonthNumb),'DD-MM-YYYY') as Dates,

  [CS_R+B]

  Resident tmpData;

Mkt:

LOAD Mkt_BU,

     Market,

     [Business Unit]

FROM

blabla2.xlsx

(ooxml, embedded labels, table is Mkt_BU_corr);

TOTO:

LOAD * FROM Mkt;

INNER JOIN(Mkt) LOAD Dates FROM tmpData2;

The problem occurs during the TOTO table building, so I guess my syntax is not correct.

The idea is afterwards to turn tmpData2 into a table that would look like

Market_BU_dates

CS_R+B

so that I can have the match.

Sorry for the quite fastidious explanation and thousands of thanks in advance for your help,

Do not hesitate to ask questions if my case is not clear.

Sincerely,

Luc

4 Replies
MVP
MVP

Re: Handling joints and cartesian products

Can you provide around 10 lines sample data in excel file? Also provide the resultant table you required !

Not applicable

Re: Handling joints and cartesian products

Hi Manish,

Thanks for your answer.

The table 'Mkt' is the following :

Mkt_BUMarketBusiness Unit
MCO Adriatics AMCO AdriaticsA
MCO Adriatics AMCO AdriaticsA
MCO Adriatics AMCO AdriaticsA
Bosnia ABosniaA
Croatia ACroatiaA
Serbia & Montenegro ASerbia & MontenegroA
Slovenia ASloveniaA
Czech Republic ACzech RepublicA
Slovakia ASlovakiaA
Bulgaria ABulgariaA
Hungary AHungaryA
Romania ARomaniaA
Greece AGreeceA
Poland APolandA
MCO Adriatics BMCO AdriaticsB
Bosnia BBosniaB
Croatia BCroatiaB
Serbia & Montenegro BSerbia & MontenegroB
Slovenia BSloveniaB
Czech Republic BCzech RepublicB
Slovakia BSlovakiaB
Bulgaria BBulgariaB
Hungary BHungaryB
Romania BRomaniaB
Greece BGreeceB
Poland BPolandB
MCO Adriatics CMCO AdriaticsC
Bosnia CBosniaC
Croatia CCroatiaC
Serbia & Montenegro CSerbia & MontenegroC
Slovenia CSloveniaC
Czech Republic CCzech RepublicC
Slovakia CSlovakiaC
Bulgaria CBulgariaC
Hungary CHungaryC
Romania CRomaniaC
Greece CGreeceC
Poland CPolandC
MCO Adriatics ALLMCO AdriaticsALL
Bosnia ALLBosniaALL
Croatia ALLCroatiaALL
Serbia & Montenegro ALLSerbia & MontenegroALL
Slovenia ALLSloveniaALL
Czech Republic ALLCzech RepublicALL
Slovakia ALLSlovakiaALL
Bulgaria ALLBulgariaALL
Hungary ALLHungaryALL
Romania ALLRomaniaALL
Greece ALLGreeceALL
Poland ALLPolandALL


And I would like to "add" a column with any of the possible dates that I got from the crosstable (basically any month from Jan 2010 or 2011 to Dec 2015), to create a key "Mkt_BU_Date".

For instance :

Mkt_BUMarketBusiness UnitDate
MCO Adriatics CMCO AdriaticsCJan-10
MCO Adriatics CMCO AdriaticsCFeb-10
MCO Adriatics CMCO AdriaticsCMar-10

Etc.

Is it any clearer or should I reformulate ?

Thanks a lot for your understanding, and your patience.

Sincerely yours,

Luc

MVP
MVP

Re: Handling joints and cartesian products

Not enough.... ! The data you provide is not having any date or month and you want to include date in final requirements.

Please provide in excel file.

Not applicable

Re: Handling joints and cartesian products

Ok, the dates come from the file I was referring to in my previous post Handling dates and cross table

I gathered it all in one excel file so that the 4 files I am trying to connect are the 4 tabs of the attached excel file

Sample_Qlik_Community.xlsx

Hope it helps (by the way I couldn't find a way to attach a file directly to my answer, is it normal ? I could attach only pictures...) and it makes it more clear.

Thanks again,

Luc