Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Button

Hi,

I have two tables having many columns.

Supplier1:

Load PartNumber,

Supplier1Name,

.

.

.

From .......

Supplier2:

Load PartNumber,

Supplier2Name,

.

.

.

From .......

Now, I want to use only one field Supplier2Name from Supplier2 in Supplier1 table but do not want to use Left Join or Right Join function.

After displaying table showing all fields of Supplier1 + Supplier2Name, want to create a button. The criteria is when I click this button, the table will show only those rows where Supplier1Name is not equal to Supplier2Name.

1 Solution

Accepted Solutions
Jason_Michaelides
Luminary Alumni
Luminary Alumni

Ah - OK!

So with your Parts/Supplier dimension table (Table1) and your Fact table (Table2) joined on the PartNo field you have a pretty standard set up - good to see!  I'm guessing you also have some kind of OrderID field in the Fact table?

Really what you want is to select the OrderIDs where MainSupplierName <> SupplierName2.  This is easy using the advanced search but using advanced search in buttons is tricky!  Therefore I'd go with Jagan's idea of a flag but with the logic you require:

Parts:

LOAD

     *,

     PartNo & '/' & MainSupplierName     AS     FlagKey

From PartSupplierData...;

Map_Suppliers:

MAPPING LOAD

     FlagKey,

     0

RESIDENT Parts;

Fact:

LOAD

     *,

     ApplyMap('Map_Suppliers',PartNo & '/' & SupplierName2,1)     AS     DiffSupplier

From OrderData...;

Then, in your button add a Select In Field action, type DiffSupplier in the field box and in the expression type 1.  This will select all orders where the PartNo and SupplierName2 combination does not equal the PartNo and MainSupplierName combination.

Hope this helps,

Jason

View solution in original post

12 Replies
Jason_Michaelides
Luminary Alumni
Luminary Alumni

Try adding a Select In Field action, putting Supplier1Name in the field and then either

<>Supplier2Name

or

="<>Supplier2Name"

in the expression box.

Hope this helps,

Jason

Not applicable
Author

Thanks for your reply.

can you provide example for below data.....? For simplicity, i have given only two fields for both tables.

PartNOSupplierName1
AABC
BDEF
CGHI
DJKL

PartNOSupplierName2
AABC
BXYZ
CMNO
DJKL

Need three columns, PartNO, SupplierName1 and SupplierName2.

Jason_Michaelides
Luminary Alumni
Luminary Alumni

Are the 2 tables only linked on PartNo and is it a one-to-one relationship?

Not applicable
Author

Both tables have only one common field i.e. PartNO and both tables have several other field but haven't mentioned due to not required at this moment.

Also, I want to use only Supplier2Name (SupplierName2) in first table.

jagannalla
Partner - Specialist III
Partner - Specialist III

Hello,

Try this:

Supplier1:

LOAD * INLINE [

    PartNO, SupplierName1

    A, ABC

    B, DEF

    C, GHI

    D, JKL

];

Map1:

Mapping

LOAD SupplierName1 as Key, SupplierName1 Resident Supplier1;

Supplier2:

LOAD *,ApplyMap('Map1',SupplierName2,1) as Flag;

LOAD * INLINE [

    PartNO, SupplierName2

    A, ABC

    B, XYZ

    C, MNO

    D, JKL

];

- In button action -> Selection in Field ( Flag) and Search String as 1.

For your reference i'm attaching sample file.

Hope it helps you.

Cheers !

Jason_Michaelides
Luminary Alumni
Luminary Alumni

Jagan - I think your code checks if any Supplier2Name has an identical Supplier1Name anywhere in the data model wherease I think what mrkachhia is asking is if Suplier1 and Supplier2 as linked by PartNo, are the same.  I'm not sure though...

mrkachhia, can the same PartNo be present more than once in either table, with different suppliers?

Jason

jagannalla
Partner - Specialist III
Partner - Specialist III

Jason - If you see the first post of mrkachhia, he had said that

After displaying table showing all fields of Supplier1 + Supplier2Name, want to create a button. The criteria is when I click this button, the table will show only those rows where Supplier1Name is not equal to Supplier2Name.

According to above content i understood that he will take the all fields of Table1 along with Supplier2Name field from Table2. Then when he click on button he need to see the result of Supplier1Name <> Supplier2Name.

I don't know whether i understood in right way or not. If anything wrong please explain me.

Not applicable
Author

Hi Jason,

Good Question!

Let me describe in more details.

Table1 is having details of part numbers and their MainSupplier....

     i.e. PartNO, MainSupplierID, MainSupplierName, MainSupplierCountry, Price, Currency, LeadTime etc.... Also, PartNO is unique and not repeating in Table1...

Table2 is having details of all outstanding orders in system..... it is posssible that PartNO is repeating more than once and also SupplierName2 would be different for the same part as due to unavailability of any part with MainSupplier, we have to order it from second source.

My aim is to find PartNOs which are currently on order with second source.

My initial post should be like this.. changes given in BOLD. Sorry for initial mistke.

Now, I want to use only one field Supplier1Name from Supplier1 in Supplier2 table but do not want to use Left Join or Right Join function.

After displaying table showing all fields of Supplier2 + SupplierName1, want to create a button. The criteria is when I click this button, the table will show only those rows where SupplierName1 is not equal to SupplierName2.

Jason_Michaelides
Luminary Alumni
Luminary Alumni

Ah - OK!

So with your Parts/Supplier dimension table (Table1) and your Fact table (Table2) joined on the PartNo field you have a pretty standard set up - good to see!  I'm guessing you also have some kind of OrderID field in the Fact table?

Really what you want is to select the OrderIDs where MainSupplierName <> SupplierName2.  This is easy using the advanced search but using advanced search in buttons is tricky!  Therefore I'd go with Jagan's idea of a flag but with the logic you require:

Parts:

LOAD

     *,

     PartNo & '/' & MainSupplierName     AS     FlagKey

From PartSupplierData...;

Map_Suppliers:

MAPPING LOAD

     FlagKey,

     0

RESIDENT Parts;

Fact:

LOAD

     *,

     ApplyMap('Map_Suppliers',PartNo & '/' & SupplierName2,1)     AS     DiffSupplier

From OrderData...;

Then, in your button add a Select In Field action, type DiffSupplier in the field box and in the expression type 1.  This will select all orders where the PartNo and SupplierName2 combination does not equal the PartNo and MainSupplierName combination.

Hope this helps,

Jason