topic Re: Joining dates in QlikView

Joining dates

Anonymous — Tue, 12 Apr 2016 12:29:35 GMT

Good day!

Colleagues, i have some simple model to understand how to join (fill) values by some key field.

I load main table from sheet S1:

Id	Date	Sales
1	01.01.2016	34
1	04.01.2016	7
1	06.01.2016	2
1	07.01.2016	5

then i need to join to this table all dates by Id field from sheet S2.

My necessary result table:

Id	Date	Sales
1	01.01.2016	34
1	02.01.2016	-
1	03.01.2016	-
1	04.01.2016	7
1	06.01.2016	2
1	07.01.2016	5
1	10.01.2016	-

Help, please!

Re: Joining dates

Chanty4u — Tue, 12 Apr 2016 12:34:02 GMT

T1:

load *,

All fields

from Table 1:

concatenate(T1)

T2:

Load

all

from

sheet 2;

Re: Joining dates

sunny_talwar — Tue, 12 Apr 2016 12:35:54 GMT

May be you want to concatenate the two tables?

Re: Joining dates

kkkumar82 — Tue, 12 Apr 2016 12:42:22 GMT

If both the sheets contain same number of fields with same names they will be automatically concatinated I mean joined in your language.

Is that what you wanted.

Re: Joining dates

sunny_talwar — Tue, 12 Apr 2016 12:45:56 GMT

Why did you remove your 1st comment? I think Master Calendar is the way to go here Kumar

Re: Joining dates

kkkumar82 — Tue, 12 Apr 2016 12:49:20 GMT

Hi Sunny,

Love to talk to you once directly if possible, over enthusiast .

I thought by looking the post that there is only one table with the first dates and need to fill the other dates, so went for suggesting master calendar, but as I re looked there are two tables that is why I removed the comment.

I thing I am correct atleast now.

Re: Joining dates

Anonymous — Tue, 12 Apr 2016 12:50:31 GMT

i don't know what in this case i must to do (Join or Concat)..

I need to get result described above..

This is test example, but in my real data there more fields (id, City and others), but dates must be joined to Main tables by Id field.

Re: Joining dates

Anonymous — Tue, 12 Apr 2016 13:13:42 GMT

Thanks.

But i thing, tables concatenating is not right solution for my problem.

For example, my first table can consist one more field, for example Warehouse id (Value 1 in field Id has matching value 3334 in Warehouse_Id field ) and in this case result table after joining dates must be:

Id	Warehouse_Id	Date	Sales
1	3334	01.01.2016	34
1	3334	02.01.2016	-
1	3334	03.01.2016	-
1	3334	04.01.2016	7
1	3334	06.01.2016	2
1	3334	07.01.2016	5
1	3334	10.01.2016	-

Thanks for help.

Re: Joining dates

jlongoria — Tue, 12 Apr 2016 14:02:58 GMT

First, please review this article on join, keep, and concatenate: Understanding Join, Keep and Concatenate

One approach given the spreadsheet data and your expected results is to outer join S1 and S2.

MyData:
LOAD Id,
Date,
Sales
FROM
[data.xlsx]
(ooxml, embedded labels, table is S1);

Outer Join (MyData)

LOAD Id,
Date
FROM
[data.xlsx]
(ooxml, embedded labels, table is S2);

Re: Joining dates

Anonymous — Wed, 13 Apr 2016 06:06:38 GMT

Thanks Dear.

In your example.

If i add some one field to S1 sheet, for examplem warehouse, Dates from S2 don't connect with it:

MyData:

LOAD Id,

Warehouse,

Date,

Sales

FROM

[Test.xlsx]

(ooxml, embedded labels, table is S1);

Outer Join (MyData)

LOAD Id,

Date

FROM

[Test.xlsx]

(ooxml, embedded labels, table is S2);

Result:

Id	Warehouse	Date	Sales
1	Main	01-01-2016	34
1	Main	04-01-2016	7
1	Main	06-01-2016	2
1	Main	07-01-2016	5
1		02-01-2016
1		03-01-2016
1		10-01-2016

Necessary result:

Id	Warehouse	Date	Sales
1	Main	01-01-2016	34
1	Main	04-01-2016	7
1	Main	06-01-2016	2
1	Main	07-01-2016	5
1	Main	02-01-2016
1	Main	03-01-2016
1	Main	10-01-2016

Re: Joining dates

perumal_41 — Wed, 13 Apr 2016 06:24:22 GMT

Try like below

Temp1 :

Load

Warehouse

[Test.xlsx]

(ooxml, embedded labels, table is S1);

Join

LOAD Id,

Date

FROM

[Test.xlsx]

(ooxml, embedded labels, table is S2);

MyData:

LOAD Id,

Warehouse,

Date,

Sales

FROM

[Test.xlsx]

(ooxml, embedded labels, table is S1);

Outer Join (MyData)

Load * Resident Temp1;

Drop table Temp1;

Re: Joining dates

Anonymous — Wed, 13 Apr 2016 06:29:51 GMT

thanks.

if i change some value in Warehouse field (in second row Man->Main1), i get strange result:

Id	Warehouse	Date	Sales
1	Main	01-01-2016	34
1	Main	02-01-2016
1	Main	03-01-2016
1	Main	06-01-2016	2
1	Main	07-01-2016	5
1	Main	10-01-2016
1	Main1	01-01-2016
1	Main1	02-01-2016
1	Main1	03-01-2016
1	Main1	04-01-2016	7
1	Main1	10-01-2016

Re: Joining dates

perumal_41 — Wed, 13 Apr 2016 06:33:52 GMT

always you want Warehouse as Main. please let me know if more then one warehouse is there m then how how is your output need

Re: Joining dates

Anonymous — Wed, 13 Apr 2016 06:43:17 GMT

First table:

Id	Warehouse	Date	Sales
1	1	01.01.2016	34
1	2	04.01.2016	7
1	3	06.01.2016	2
1	4	07.01.2016	5

Second:

Id	Date
1	01.01.2016
1	02.01.2016
1	03.01.2016
1	10.01.2016

Result:

Id	Warehouse	Date	Sales
1	1	01.01.2016	34
1	1	02.01.2016
1	1	03.01.2016
1	1	10.01.2016
1	2	04.01.2016	7
1	2	02.01.2016
1	2	03.01.2016
1	2	10.01.2016
1	3	06.01.2016	2
1	3	02.01.2016
1	3	03.01.2016
1	3	10.01.2016
1	4	07.01.2016	5
1	4	02.01.2016
1	4	03.01.2016
1	4	10.01.2016

Re: Joining dates

perumal_41 — Wed, 13 Apr 2016 06:53:06 GMT

same output above script . any have attaching sample for your reference

Re: Joining dates

Anonymous — Wed, 13 Apr 2016 07:09:33 GMT

many thanks for help!

and i have one more problem to finish my task...

in first and second source i add one more field Nomenclature Id.

I the same way i need to add dates and nomenclature id from S2.

Please, help..

Test examples in attachment:

necessary result:

Id	Warehouse	Date	Nomenclature Id	Sales
1	1	01.01.2016	a	34
1	1	01.01.2016	b
1	1	02.01.2016	b
1	1	03.01.2016	c
1	1	10.01.2016	a
1	2	04.01.2016	a	7
1	2	01.01.2016	b
1	2	02.01.2016	b
1	2	03.01.2016	c
1	2	10.01.2016	a
1	3	06.01.2016	b	2
1	3	01.01.2016	b
1	3	02.01.2016	b
1	3	03.01.2016	c
1	3	10.01.2016	a
1	4	07.01.2016	d	5
1	4	01.01.2016	b
1	4	02.01.2016	b
1	4	03.01.2016	c
1	4	10.01.2016	a

Re: Joining dates

perumal_41 — Wed, 13 Apr 2016 07:14:41 GMT

please upload sample data

Re: Joining dates

Kushal_Chawda — Wed, 13 Apr 2016 07:17:18 GMT

andreyfcdk91

People are here to help you, but that doesn't mean that every time you add the one field and show the results are incorrect. Instead request you to share a sample excel which consist of all the required field and also show the expected output for the same, so that it will be easy to find the solution.

QlikCommunity Tip: How to get answers to your post?

Re: Joining dates

Anonymous — Wed, 13 Apr 2016 07:26:10 GMT

in attachment..

Re: Joining dates

Anonymous — Wed, 13 Apr 2016 07:36:20 GMT

If i have more time to find solution by myself, of course, i have do it..

But this source has some people, who have ability help me operatively..

If you do not like the method of contacting my post, sorry...