Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

evgeniyita
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:

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

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: Using the "MonthlyWeek" field in both tables

maybe this?

1.png

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;

View solution in original post

7 Replies
Highlighted
MVP
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:

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;



Highlighted
evgeniyita
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
evgeniyita
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
MVP

Re: Using the "MonthlyWeek" field in both tables

Could you post your data (excel)?

Highlighted
evgeniyita
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:

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

Highlighted
MVP
MVP

Re: Using the "MonthlyWeek" field in both tables

maybe this?

1.png

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;

View solution in original post

Highlighted
evgeniyita
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:

load T1...


load T2...


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


Join(T1) load * resident T2;

Drop table T2;