QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Contributor

Using the "MonthlyWeek" field in both tables

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:

City,

Store,

Date,

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

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

Value1

from 1.xlsx;

Concatenate(Data)

Data2

City,

Store,

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

Value2

from 2.xlsx;

With my best regards,

Evgeniy

1 Solution

Accepted Solutions
Highlighted
MVP

Re: Using the "MonthlyWeek" field in both tables

maybe this?

T2:

StoreName,

Day,

[Value2(Plan)]

FROM

[2.xls]

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

join (T2)

City,

StoreName,

////     Date,

////     Day,

MonthlyWeek

//     [Value1(Fact)]

FROM

[1.xls]

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

T1:

StoreName,

Date,

Day,

MonthlyWeek,

[Value1(Fact)]

FROM

[1.xls]

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

DROP Table X;

7 Replies
Highlighted
MVP

Re: Using the "MonthlyWeek" field in both tables

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

Data:

City, Store, Date,

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

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

Value1

from 1.xlsx;

left join (Data)

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

Value2

from 2.xlsx;

Highlighted
Contributor

Re: Using the "MonthlyWeek" field in both tables

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!

Highlighted
Contributor

Re: Using the "MonthlyWeek" field in both tables

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

Highlighted
MVP

Re: Using the "MonthlyWeek" field in both tables

Could you post your data (excel)?

Highlighted
Contributor

Re: Using the "MonthlyWeek" field in both tables

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:

City, Store, Date,

Day,

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

Value1

from 1.xlsx;

Concatenate(Data)

City, Store,

Day,

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

Value2

from 2.xlsx;

With my best regards,

Evgeniy

Highlighted
MVP

Re: Using the "MonthlyWeek" field in both tables

maybe this?

T2:

StoreName,

Day,

[Value2(Plan)]

FROM

[2.xls]

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

join (T2)

City,

StoreName,

////     Date,

////     Day,

MonthlyWeek

//     [Value1(Fact)]

FROM

[1.xls]

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

T1:

StoreName,

Date,

Day,

MonthlyWeek,

[Value1(Fact)]

FROM

[1.xls]

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

DROP Table X;

Highlighted
Contributor

Re: Using the "MonthlyWeek" field in both tables

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: