topic Re: Adding records in QlikView

Adding records

Fri, 27 Sep 2013 04:57:41 GMT

Hi All,

I have 3 tables Tab1, Tab2

Tab1 structure as follows:

Project Number	Project Name	Type Code	Rate	Period	Data
1	A	CNT	1500	Jan-13	1
2	B	PMT	1500	Feb-13	2
3	C	PMT	2500	Mar-13	3
4	D	CNT	2700	Apr-13	4

Tab2 Structure as Follows:

Project Number	Project Name	Type Code	Rate	Period	Data
1	A	CNT	1500	Jan-13	1
2	B	PMT	1500	Feb-13	2
3	C	PMT	2500	Mar-13	3
5	E	CNT	1500	May-13	5
6	F	PMT	2700	Jun-13	6.1
7	G	CNT	3200	Jul-13	6.3
8	H	PMT	2500	Aug-13	7.2

My requirement is I want to add the records which are not available in Tab1,

Table should be as follows

Project Number	Project Name	Type Code	Rate	Period	Data
1	A	CNT	1500	Jan-13	1
2	B	PMT	1500	Feb-13	2
3	C	PMT	2500	Mar-13	3
4	D	CNT	2700	Apr-13	4
5	E	CNT	1500	May-13	5
6	F	PMT	2700	Jun-13	6.1
7	G	CNT	3200	Jul-13	6.3
8	H	PMT	2500	Aug-13	7.2

I used concatenation and joins but I’m getting error.

Please someone have a look into this, give me suggestion.

Thanks,

Chiru

Re: Adding records

er_mohit — Fri, 27 Sep 2013 12:42:43 GMT

See the attached dummy app

Re: Adding records

tresB — Fri, 27 Sep 2013 12:46:12 GMT

Try like:

Final:

Load * from Tab2;

Load * from Tab1 Where Not Exists([Project Number]);

Re: Adding records

Fri, 27 Sep 2013 12:59:01 GMT

Try:

[Project]:

LOAD Project Number,

Project Name,

Type Code,

Rate,

Period,

Data

FROM Tab1

CONCATENATE(Project)

LOAD Project Number,

Project Name,

Type Code,

Rate,

Period,

Data

FROM Tab2

WHERE NOT EXISTS (Project Number)

Re: Adding records

Anonymous — Fri, 27 Sep 2013 13:18:43 GMT

Chiru,

Contatenation is what you need. What the error is?

BTW, when loading the 2nd table, use condition where not exists("Project Number") if you want to avoid duplicates.

Regards,

Michael

Re: Adding records

Fri, 27 Sep 2013 13:22:42 GMT

The best way is to concatenate the tables and then perform a distinct load; Therefore;

Load

From Table1;

Concatenate (Table1) Load

From Table1;

FinalTable

NoConcatenate Load Distinct

Resident Table1;

Drop Table1;

Let me know if this is correct. I hope I helped.

Regards

Steve

Re: Adding records

jonathandienst — Fri, 27 Sep 2013 13:32:24 GMT

This should do it:

Concatenate

LOAD [Project Number],

[Project Name',

...

FROM Table2

Where Not Exists([Project Number]);

Regards

Jonathan

Re: Adding records

marcus_sommer — Fri, 27 Sep 2013 14:08:12 GMT

Concatenate from both tables and afterwards a distinct load from these table will work. Also you could build a unique key from your fields and use this key in a where-clause.

- Marcus

Re: Adding records

Thu, 03 Oct 2013 07:57:10 GMT

Hi Steve,

Thanks for your Reply, it's working fine now.

Thanks,

Chiru