topic Scripting / Cartesian products and conditions in QlikView

Scripting / Cartesian products and conditions

Wed, 09 Dec 2009 23:09:46 GMT

Hello,

Could you please help me to solve this problem? I guess this should be quite easy to do with some experience, but I'm quite a beginner in QlikView development.

Here is my problem

Source data

There are two source tables, with few fields

Table: TODAY

Fields: Customer, Country, Name

Table: HISTORIC

Fields: Date, Customer, Country, Qtty

In QV, I would like to get three tables

Table: DIMENSIONS

Fields: Id, Customer, Country

Comment: In this table, there should all possible couples (Customer/Country) from the source table TODAY and the source table HISTORIC and an appropriate Id

Table: TODAY

Fields: Id, Name

Comment: It is the Id that can be find in DIMENSIONS, and the name from the source table TODAY

Table: HISTORIC

Fields: Id, Date, Qtty

Comment: It should be the Cartesian product Id/Date, with all possible Id that can be found in DIMENSIONS and all Date that can be found in the source table HISTORIC. If there is no quantity for this couple, then the value for qtty should be '0' (otherwise, it is the value from the source table HISTORIC)

The creation of DIMENSIONS and TODAY is ok, but I really face problems with HISTORIC

I guess it should look like

- Create the Cartesian product Id/Date

- Add the field Qtty depending on the existence of the couple Id/Date in the source table HISTORIC

Your help would be very invaluable for me!

Pierre

Scripting / Cartesian products and conditions

Oleg_Troyansky — Wed, 09 Dec 2009 23:25:39 GMT

Pierre,

if you don't mind posting your script (or, even better, the whole QVW), we'd be able to show you how to tweak it to get what you need.

cheers,

Scripting / Cartesian products and conditions

Thu, 10 Dec 2009 00:10:58 GMT

Hello

Yes you are right

I have attached the basic .qvw file

I thank you a lot !

Pierre

Scripting / Cartesian products and conditions

Mon, 14 Dec 2009 22:15:31 GMT

Hello,

Basically, the problem is the following

There is a table A with two fields : Id and Quantity:

Record 1 : Id1, 12

There is a table B with one field : Id

Record 1 : Id1

Record 2 : Id2

I would like to add a column in table B with the quantity found in A, or 1 if there is no correspondence

Then, table B would be :

Record 1 : Id1, 12

Record 2 : Id2, 1

Do you have any idea ?

Thanks

Scripting / Cartesian products and conditions

johnw — Tue, 15 Dec 2009 06:40:28 GMT

Here's one way (untested):

LEFT JOIN (B)
LOAD Id, Quantity as TempQuantity
RESIDENT A
;
DROP TABLE A
;
LEFT JOIN (B)
LOAD Id, if(len(TempQuantity),TempQuantity,1) as Quantity
RESIDENT B
;
DROP FIELD TempQuantity
;

Scripting / Cartesian products and conditions

Tue, 15 Dec 2009 15:18:34 GMT

Hi John,

It perfectly works !

I thank you for having taken time to help me

Pierre