topic Re: Handling joints and cartesian products in QlikView

Handling joints and cartesian products

Fri, 20 Nov 2020 18:02:30 GMT

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

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

Re: Handling joints and cartesian products

MK_QSL — Fri, 24 Jul 2015 15:06:17 GMT

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

Re: Handling joints and cartesian products

Mon, 27 Jul 2015 08:22:49 GMT

Hi Manish,

Thanks for your answer.

The table 'Mkt' is the following :

Mkt_BU	Market	Business Unit
MCO Adriatics A	MCO Adriatics	A
MCO Adriatics A	MCO Adriatics	A
MCO Adriatics A	MCO Adriatics	A
Bosnia A	Bosnia	A
Croatia A	Croatia	A
Serbia & Montenegro A	Serbia & Montenegro	A
Slovenia A	Slovenia	A
Czech Republic A	Czech Republic	A
Slovakia A	Slovakia	A
Bulgaria A	Bulgaria	A
Hungary A	Hungary	A
Romania A	Romania	A
Greece A	Greece	A
Poland A	Poland	A
MCO Adriatics B	MCO Adriatics	B
Bosnia B	Bosnia	B
Croatia B	Croatia	B
Serbia & Montenegro B	Serbia & Montenegro	B
Slovenia B	Slovenia	B
Czech Republic B	Czech Republic	B
Slovakia B	Slovakia	B
Bulgaria B	Bulgaria	B
Hungary B	Hungary	B
Romania B	Romania	B
Greece B	Greece	B
Poland B	Poland	B
MCO Adriatics C	MCO Adriatics	C
Bosnia C	Bosnia	C
Croatia C	Croatia	C
Serbia & Montenegro C	Serbia & Montenegro	C
Slovenia C	Slovenia	C
Czech Republic C	Czech Republic	C
Slovakia C	Slovakia	C
Bulgaria C	Bulgaria	C
Hungary C	Hungary	C
Romania C	Romania	C
Greece C	Greece	C
Poland C	Poland	C
MCO Adriatics ALL	MCO Adriatics	ALL
Bosnia ALL	Bosnia	ALL
Croatia ALL	Croatia	ALL
Serbia & Montenegro ALL	Serbia & Montenegro	ALL
Slovenia ALL	Slovenia	ALL
Czech Republic ALL	Czech Republic	ALL
Slovakia ALL	Slovakia	ALL
Bulgaria ALL	Bulgaria	ALL
Hungary ALL	Hungary	ALL
Romania ALL	Romania	ALL
Greece ALL	Greece	ALL
Poland ALL	Poland	ALL

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_BU	Market	Business Unit	Date
MCO Adriatics C	MCO Adriatics	C	Jan-10
MCO Adriatics C	MCO Adriatics	C	Feb-10
MCO Adriatics C	MCO Adriatics	C	Mar-10

Etc.

Is it any clearer or should I reformulate ?

Thanks a lot for your understanding, and your patience.

Sincerely yours,

Luc

Re: Handling joints and cartesian products

MK_QSL — Mon, 27 Jul 2015 08:26:16 GMT

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.

Re: Handling joints and cartesian products

Mon, 27 Jul 2015 09:23:09 GMT

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