Scripting / Cartesian products and conditions

[#1]

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

[Oleg_Troyansky]

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,

[#3]

Hello

Yes you are right

I have attached the basic .qvw file

I thank you a lot !

Pierre

[#4]

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

[johnw · Accepted solution]

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
;

[#6]

Hi John,

It perfectly works !

I thank you for having taken time to help me

Pierre