topic Re: Concatenate two tables with focus on WeekDay in QlikView

Concatenate two tables with focus on WeekDay

Anonymous — Tue, 13 Feb 2018 13:52:36 GMT

Hello,

I'd like to concatenate these two tables.

But the problem is that WeekDay field from the first isn't equals to WeekDay from the second.

How to concatenate in this case and apply a "date" format to the crosstable's field? (I can't change WeekDay from the first table to text)

Example:

Table1:

load ID, Sales,

Store,

WeekDay(Date) as WeekDay, // Mon, Tue, etc.

from source_1;

CrossTable('WeekDay', 'Sales', 2)

Table2:

load ID, Store

Monday as Mon,

Tuesday as Tue,

Wednesday as Wed

.. etc.

from source_2;

With best regards,

Evgeniy Sharahov

Re: Concatenate two tables with focus on WeekDay

sunny_talwar — Tue, 13 Feb 2018 13:57:43 GMT

May be like this

Table1:

LOAD ID, Sales,

Store,

Text(WeekDay(Date)) as WeekDay, // Mon, Tue, etc.

from source_1;

CrossTable(WeekDay, Sales, 2)

Table2:

LOAD ID,

Store

Monday as Mon,

Tuesday as Tue,

Wednesday as Wed

.. etc.

from source_2;

Or this

Table1:

LOAD ID, Sales,

Store,

WeekDay(Date) as WeekDay, // Mon, Tue, etc.

from source_1;

Table2:

CrossTable(Week, Sales, 2)

LOAD ID,

Store

Monday as Mon,

Tuesday as Tue,

Wednesday as Wed

.. etc.

from source_2;

Concatenate (Table1)

LOAD ID,

Store,

Dual(Week, Match(Week, 'Mon', 'Tue', 'Wed', 'Thur', 'Fri', 'Sat', 'Sun')) as WeekDat,

Sales

Resident Table2;

DROP Table Table2;

Re: Concatenate two tables with focus on WeekDay

Anonymous — Tue, 13 Feb 2018 14:19:59 GMT

Thank you for the quick response!

I've chosen the second. I'm afraid but It doesn't work correctly. How to fix them?

Re: Concatenate two tables with focus on WeekDay

sunny_talwar — Tue, 13 Feb 2018 14:41:53 GMT

What is the issue you run into?

Re: Concatenate two tables with focus on WeekDay

Anonymous — Tue, 13 Feb 2018 14:47:28 GMT

I've got two Sundays by the second one. Why? 😃 It isn't correct

Re: Concatenate two tables with focus on WeekDay

sunny_talwar — Tue, 13 Feb 2018 14:50:45 GMT

By the second one? Can you show this in an image?

Re: Concatenate two tables with focus on WeekDay

Anonymous — Tue, 13 Feb 2018 14:56:37 GMT

There is a list with two Sundays.

Re: Concatenate two tables with focus on WeekDay

sunny_talwar — Tue, 13 Feb 2018 14:59:08 GMT

Try this

Concatenate (Table1)

LOAD ID,

Store,

Dual(Week, (Match(Week, 'Sun', 'Mon', 'Tue', 'Wed', 'Thur', 'Fri', 'Sat') - 1)) as WeekDat,

Sales

Resident Table2;

Re: Concatenate two tables with focus on WeekDay

Anonymous — Tue, 13 Feb 2018 15:05:16 GMT

Two Saturdays . What's wrong?!

Re: Concatenate two tables with focus on WeekDay

sunny_talwar — Tue, 13 Feb 2018 15:07:53 GMT

Can you run this for me

Table1:

LOAD ID, Sales,

Store,

WeekDay(Date) as WeekDay, // Mon, Tue, etc.

Num(WeekDay(Date)) as WeekDayNum

from source_1;

and then create a table box with WeekDay and WeekDayNum fields and share a screenshot

Don't add the crosstable load after this...

Re: Concatenate two tables with focus on WeekDay

Anonymous — Tue, 13 Feb 2018 15:18:07 GMT

Thank you very much, Sunny Talwar!

Probably, correct this one:)

Dual(WeekDay, (Match(WeekDay, 'Mon', 'Tue', 'Wed', 'Thur', 'Fri', 'Sat', 'Sun') - 1)) as Day,