Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
macaulay
Creator
Creator

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

11 Replies
beck_bakytbek
Master
Master

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
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
macaulay
Creator
Creator
Author

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
Creator
Creator
Author

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
Partner - Master III
Partner - Master III

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
Creator
Creator
Author

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
Partner - Master III
Partner - Master III

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
Creator
Creator
Author

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

jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein