Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

macaulay
Contributor

Access 2 different sources with same data, load one privileged

Dear all,

I got two different sources which both give me a date-field "Projektstart" ( the start date of a project). For usual I receive the data from Source 1, but sometimes it is necessary to take it from Source 2, for example when Source 1 doesn't contain data for a project. In 95% of all cases both sources will contain data, in this case I wan't to receive the data from Source 1, because Source 1 may contain wrong data.

Is there a possibility to allocate to both sources the same field in QLIK and how can I instruct QLIK, to take the data from source 1 favored?

Thanks for the answers,

Best regards

Tom Müller

Tags (3)
11 Replies
beck110979
Valued Contributor III

Re: Access 2 different sources with same data, load one privileged

Hi Tom,

i have an idea, maybe you can use in your script-area: sub call - procedure,

http://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/ScriptControlSt...

i hope that helps

Beck

MVP
MVP

Re: Access 2 different sources with same data, load one privileged

Assuming you have some sort if row ID (lets call it ID) to relate the rows in the two sources, then something like this:

Qualify *;

UnQualify ID;

T1:

LOAD ID,

  Field1,

  Field2,

  ...

FROM Source1;

T2:

LOAD ID,

  Field1,

  Field2,

  ...

FROM Source2;

UnQualify *;

Join(T1)

LOAD *

Resident T2;

Result:

LOAD ID,

  If(Len(T2.Field1) > 0, T2.Field1, T1.Field1) as Field1;

  If(Len(T2.Field2) > 0, T2.Field2, T1.Field2) as Field2;

  ...

Resident T1;

DROP Table T1, T2;

Adapt the field names and FROM sources to your requirements. I am also assuming that you want data from source2, and fill in from source1 if nothing in source2. If its the other way round, swap the statements around in the last LOAD.

macaulay
Contributor

Re: Access 2 different sources with same data, load one privileged

Thank you, I will try this one. One question to the last part:

Result:

LOAD ID,

  If(Len(T2.Field1) > 0, T2.Field1, T1.Field1) as Field1;

  If(Len(T2.Field2) > 0, T2.Field2, T1.Field2) as Field2;

  ...

Resident T1;


When my Field 1 is the keyfield (project number) and my Field 2 is the "Projektdatum", do I have to Load it exactly like this or can I leave out the first column?

Thank you.

macaulay
Contributor

Re: Access 2 different sources with same data, load one privileged

Jonathan,

I tried it out, my loading script looks like this.

LOAD.JPG

But when loading I get an error:

Error.JPG

Do you have an idea what I am doing wrong?

Thank you

shraddha_g
Honored Contributor III

Re: Access 2 different sources with same data, load one privileged

Paranthesis are missing in Len().

Try:

If(Len(T2.Projekt ) >0,T2.Projekt,T1.Projekt) as Projekt,

If(Len(T2.Projektstart ) >0,T2.Projektstart,T1.Projektstart) as Projekt,

macaulay
Contributor

Re: Access 2 different sources with same data, load one privileged

Thanks for your answer Shraddha,

seems better, but I get another failure.

Error.JPG

To me it seems as if T1 wasn't defined, did I do something wrong at the top of the script?

Thank you.

shraddha_g
Honored Contributor III

Re: Access 2 different sources with same data, load one privileged

Try,

Load ID,

If(Len(T2.Projekt ) >0,T2.Projekt,T1.Projekt) as Projekt , //remove semicolon from here and put ','(comma)

If(Len(T2.Projektstart ) >0,T2.Projektstart,T1.Projektstart) as Projekt //remove semicolon from here

resident T1;

macaulay
Contributor

Re: Access 2 different sources with same data, load one privileged

I already removed the semicolon, the mistake appears with commata. Should have mentioned this, sorry.

In the script "T1" (at the end) is marked red, so I think the mistake lies anywhere before. Here is the current script:

Error.JPG

MVP
MVP

Re: Access 2 different sources with same data, load one privileged

Sorry about the semi-colons, they should have been commas.

I said>>Assuming you have some sort if row ID (lets call it ID)...


You have no ID field, so the whole concept does not work even after resolving the syntax errors. So you have no common field and the result will be a cross join which will not get useful results.

Community Browser