Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi! I'm new to Qlik Sense, already explored a lot, but i'm having a hard time with this issue:
I have a MainTable and a AuxTable, the only goal of the AuxTable is to be a manual input of data for very specific subjects. The thing is, i have a column in MainTable with 'yes' and 'no' values and i need it to be inverted if the id existis in this AuxTable.
An example:
MainTable now:
id | order_id | date | status |
12343 | 443 | 01/02/2020 | yes |
AuxTable:
id | order_id | new_date |
12343 | 443 | 13/02/2020 |
New MainTable should be:
id | order_id | date | status |
12343 | 443 | 01/02/2020 | No |
I was able to make it work without much effort, using this logic:
=if(match(MainTable.Id, AuxTable.id),if(status= 'yes', 'no', 'sim'),status)
but the problem is: on the object table, when i try to filter by 'no' the table returns only the values that exists in the AuxTable, ignoring all the other that were originally 'no' from the status field of the MainTable. How can i make it to return both original MainTable´s 'no' and the 'no' from my AuxTable?
You seem to be handling this on the front end, rather than in script, which isn't ideal.
I'd suggest something along the lines of:
TempTable:
Load * From MainTable;
Join
Load * From AuxTable;
Noconcatenate
Load id, order_id, date, if(IsNull(new_date),status,'No') as status
Resident TempTable;
Drop Table TempTable;
@Gahstyle You need to create a status field in script so that it's value doesn't change.
Aux:
Load * Inline [
id order_id date status
12343 443 01/02/2020 No ](delimiter is '\t');
Main:
NoConcatenate
Load *, if(Exists(id), if(status='yes','no',if(status='no','yes')),status) as final_status
Inline [
id order_id date status
12343 443 01/02/2020 yes
12344 443 01/02/2020 yes ](delimiter is '\t');
Drop Table Aux;
Note: if names of ID field are not same in both tables then you need to use exists(id_ name_aux_table, id_name_main_table). You can now use final_status field as a filter