topic Re: unable to put dates into one table in QlikView

unable to put dates into one table

Karim_Khan — Wed, 25 Nov 2020 16:16:04 GMT

Hi Team,

it might be little lengthy...

PFA file there are basically 4 tables TABLE A consist of common field which is able to link to remaining 3 tables

TABLE A.SMS_ID=TABLE B.MSG_ID

then TABLE C and D are getting concatenate and after that joining to TABLE A

DATA:

LOAD

TABLE C

....;

CONCATENATE

TABLE D

....;

TABLE A:

...;

LEFT JOIN (TABLE A)

RESIDENT DATA;

till here am getting the every thing properly but in my requirement we need to show all dates which are coming from 3 different table shoud come into single table i have tried it thorugh same filed name but it is giving me Synthetic key.

then i have tried belwo logic;

if(ISNULL(CH.DATE1),DATE1) as NEW_DATE1,

if(ISNULL(CH.DATE2),DATE2) as NEW_DATE2,

but am getting null values

Pls suggest.

Regards,

New to QlikView‌

Re: unable to put dates into one table

Sergey_Shuklin — Tue, 09 May 2017 15:27:42 GMT

Hi, Karim!

Can you post a picture from a table viewer, please? (Ctrl+T)

Re: unable to put dates into one table

Digvijay_Singh — Tue, 09 May 2017 15:38:58 GMT

The data in TABLE C and TABLE D are exactly same, any reason for that. When you concatenate you will have duplicate records? any reason for having same data in both the tables or its just a mistake?

Re: unable to put dates into one table

Digvijay_Singh — Tue, 09 May 2017 16:13:37 GMT

This generates your output but really don't understand much about the data sample.

[TABLE A]:

Load STATUS, SMS_ID inline [

SR_ID, STATUS, SMS_ID

11, SUCCESS, 6

12, FAILED, 8

13, OPEN, 7

22, SUCCESS, 9

72, SUCCESS, 2 ];

left join([TABLE A])

Load MSG_ID as SMS_ID, CH.DATE1 as DATE1, CH.DATE2 as DATE2, STATUS inline [

MSG_ID, CH.DATE1, CH.DATE2, STATUS

6, 8/10/2015, 12/11/2015, SUCCESS

8, 8/11/2015, 12/12/2015, FAILED

7, 8/12/2015, 12/13/2015, OPEN

9, 8/13/2015, 12/14/2015, SUCCESS

2, 8/14/2015, 12/15/2015, SUCCESS ];

[TABLE C]:

Load * inline [

SR_ID DATE1 DATE2 STATUS

11 10/10/2016 11/11/2016 SUCCESS

12 10/11/2016 11/12/2016 FAILED

13 10/12/2016 11/13/2016 OPEN

22 10/13/2016 11/14/2016 SUCCESS

72 10/14/2016 11/15/2016 SUCCESS ](delimiter is spaces);

Concatenate([TABLE C])

Load * inline [

SR_ID DATE1 DATE2 STATUS

11 10/10/2016 11/11/2016 SUCCESS

12 10/11/2016 11/12/2016 FAILED

13 10/12/2016 11/13/2016 OPEN

22 10/13/2016 11/14/2016 SUCCESS

72 10/14/2016 11/15/2016 SUCCESS ](delimiter is spaces);

Concatenate([TABLE A])

Load *

RESIDENT [TABLE C];

Drop table [TABLE C];

Re: unable to put dates into one table

Karim_Khan — Wed, 10 May 2017 13:38:34 GMT

TNX