Two tables, two id's and names, only use first name

[matthewp]

I have the following load in tab 1 i have

LOAD

SIAA.C_ID as [Customer ID],

SIAA.C_NAME as [Customer name]

FROM

[$(vDetailsQVDDirectory)\SIAA.qvd]

(qvd);

Then in tab 2 i have

PreIT:

Concatenate

LOAD

[Customer ID],

[Customer name]

FROM

[$(vDetailsQVDDirectory)\CTA.qvd]

(qvd);

What i want is if there is a matching Customer ID in both tabs i want it to only use the name from tab 1 (SIAA.C_NAME)

[sunny_talwar]

This is what you need:

Final:

NoConcatenate

LOAD distinct [Customer ID],

if( len(trim(Flag1))>0 or (len(trim(Flag2))>0 and len(trim(Flag1))>0), [Customer name],

[Customer name1] ) as [Customer Name]

resident Data

[sunny_talwar]

There might be simpler way to do this:

Final:

NoConcatenate

LOAD DISTINCT *,

[Customer ID],

Alt([Customer name], [Customer name1]) as [Customer Name New]

Resident Data;

DROP Table Data;

[matthewp]

ok looks like that code is compiling however it is causing issues with the code i have in tab 3 saying:

Table not found

Dateislandtemp:

load Distinct

[Month] as [Island Month],

[Year period] as [Island Year period]

Resident Details

Order by   [Year period] desc

do i need to rearrange this FINAL table code?

[sunny_talwar]

Is Details table the one you dropped? May be you have to use the new name (Final as per the above code)

Dateislandtemp:

load Distinct

[Month] as [Island Month],

[Year period] as [Island Year period]

Resident Final

Order by   [Year period] desc;

[matthewp]

What about approaching this differently and rather than using name as the dimension in the chart, use ID as the dimension but make it select the name from the first table using a calculated dimension or something like:

=IF([Customer ID] = [OLD Customer ID], [OLD Customer name],[Customer name])

[sunny_talwar]

I still don't have a great amount of familiarity with how your data is structured, but do give it a try and see how it looks. May be it does work out for you

[matthewp]

code all compiles now, still in exactly the same position as i was at the begining

[sunny_talwar]

Would it be possible to share your script?

[sunny_talwar]

You were not just joining on Customer ID and Customer name. There are other fields where you joining these two tables on it seems. Are you sure that for a particular table this information is always the same in both the tables? If this is not true, than your join is not going to take place correctly. (just as it did not work when Customer names were different. One way would be to rename them all to add 1 -> For instance Invoice Date1 and just join on Customer ID.

and then use the same if statement to choose between the first table's data when it is available or use the other table's data.

[matthewp]

no not always same, sometimes id only in 1 sometimes id only in 2,, its just when id in both then use name from table 1