topic Re: Using the "MonthlyWeek" field in both tables in QlikView

Using the "MonthlyWeek" field in both tables

Anonymous — Wed, 21 Mar 2018 12:44:32 GMT

Hello!

Description:

There are 2 tables. The 2nd table contains values which are repeatable for each week (doesn't contain a "MonthlyWeek" field).

Question / Problem:

How to add the "MonthlyWeek" field to the 2nd table in a better way?! (fields from both tables will be used in one view)

- I've tried to add subfield( '1;2;3;4;5' , ';') as MonthlyWeek to the second one but I don't think that it's a good solution.

Data:

load

City,

Store,

Date,

Day, // Mon, Tue, Wed ...

Ceil(Day(Date)/7) as MonthlyWeek, // 1, 2, 3...

Value1

from 1.xlsx;

Concatenate(Data)

Data2

load

City,

Store,

Day, // Mon, Tue, Wed ...

Value2

from 2.xlsx;

With my best regards,

Evgeniy

Re: Using the "MonthlyWeek" field in both tables

maxgro — Wed, 21 Mar 2018 12:53:00 GMT

Do you mean you have the same Value2 for every City, Sotre, Day (and no duplicates for these 3 fields)?

Data:

load

City, Store, Date,

Day, // Mon, Tue, Wed ...

Ceil(Day(Date)/7) as MonthlyWeek, // 1, 2, 3...

Value1

from 1.xlsx;

left join (Data)

load

City, Store, Day, // Mon, Tue, Wed ...

Value2

from 2.xlsx;

Re: Using the "MonthlyWeek" field in both tables

Anonymous — Wed, 21 Mar 2018 13:10:05 GMT

Hi Massimo Grossi,

Not quite. The 2nd table contains the data like:

Store, Day, Value2

Store1, Mon, 0

Store1, Thu, 1

Store1, Wed, 0

... (7 weeks day)

Store2, Mon, 1

Store2, Thu, 0

Store2, Wed, 1

...(7 weeks day)

These values are the same for each number of monthly week.

Thanks for the quick response!

Re: Using the "MonthlyWeek" field in both tables

Anonymous — Wed, 21 Mar 2018 14:25:07 GMT

It would be better to say that:

if I use "left join" I won't see the missing (hidden) data

if I use "Concatenate" I will see the full picture but a lot of additional rows will be added

What should I do? Are there just two option in this case?

With my best regards,

Evgeniy

Re: Using the "MonthlyWeek" field in both tables

maxgro — Wed, 21 Mar 2018 20:04:15 GMT

Could you post your data (excel)?

Re: Using the "MonthlyWeek" field in both tables

Anonymous — Thu, 22 Mar 2018 08:00:35 GMT

Hi Massimo Grossi,

I've attached just sample files based on the existing data.

Hope, It will be enough to understand the idea.

Description:

Value1 = Fact flag

Value2 = Plan flag

I will compare Value1 with Value2 in one view by MonthlyWeek and Day

I think, I need to look at "concatenate" and left like this:

Data:

load

City, Store, Date,

Day,

Ceil(Day(Date)/7) as MonthlyWeek,

Value1

from 1.xlsx;

Concatenate(Data)

load

City, Store,

Day,

subfield( '1;2;3;4;5' , ';') as MonthlyWeek,

Value2

from 2.xlsx;

With my best regards,

Evgeniy

Re: Using the "MonthlyWeek" field in both tables

maxgro — Thu, 22 Mar 2018 10:38:50 GMT

maybe this?

T2:

LOAD City,

StoreName,

Day,

[Value2(Plan)]

FROM

[2.xls]

(biff, embedded labels, table is Sheet1$);

join (T2)

LOAD Distinct

City,

StoreName,

//// Date,

//// Day,

MonthlyWeek

// [Value1(Fact)]

FROM

[1.xls]

(biff, embedded labels, table is Sheet1$);

T1:

LOAD City,

StoreName,

Date,

Day,

MonthlyWeek,

[Value1(Fact)]

FROM

[1.xls]

(biff, embedded labels, table is Sheet1$);

DROP Table X;

Re: Using the "MonthlyWeek" field in both tables

Anonymous — Thu, 22 Mar 2018 13:48:40 GMT

A very good solution! Thank you for finding the time to help me!

It works fine based on this sample data, but the real data tables are different in some fields so it has also added lots of rows

I've tried based on your suggestion:

load T1...

load T2...

Join (T2) load distinct... resident T1;

Join(T1) load * resident T2;

Drop table T2;