Access 2 different sources with same data, load one privileged

[Anonymous]

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

[beck_bakytbek]

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

[jonathandienst]

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.

[Anonymous]

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.

[Anonymous]

Jonathan,

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

But when loading I get an error:

Do you have an idea what I am doing wrong?

Thank you

[shraddha_g]

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,

[Anonymous]

Thanks for your answer Shraddha,

seems better, but I get another failure.

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]

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;

[Anonymous]

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:

[jonathandienst]

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.