Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
EugeniaCastilla
Contributor
Contributor

Crosstable is duplicating binary fields

Hello!

I am doing a simple script where I originally have a table that with one column that identifies the user (ID) and more columns of the type " has_diabities " which are binary fields (0 /1). The user wants to create a filter containing the different options (has_diabities, is_overweight, has_cronic_disease ...etc). My plan was:

  1. Create a crosstable
  2. Filter that crosstable to value=1
  3. Be happy 

However when I do so, the crosstable duplicates de values for each user and each column. Let me explain:

Record por patient 0 in original table:

EugeniaCastilla_0-1658444414568.png

What my cross table is showing for patient 0:

EugeniaCastilla_1-1658444447065.png

A you can see, Qlik is making a new row for each possible value of the field F.Condition. For example, it didn't do so for the value (HIV) because in the dataset, HIV only has 0 as a value, never a 1. What is happening? x)

Here is my code:


Orig:
load DISTINCT
"" as ID_,
IF(has_COPD=0, 'No','Yes') AS COPD,
has_Cardiovascular_disease AS Cardiovascular_disease,
has_Diabetes AS Diabetes ,
has_HIV AS HIV,
has_Liver_dysfunction AS Liver_dysfunction,
"has_Mild_renal_insufficiency_(ClCr_=_50-90mL/min)" AS Mild_renal_insufficiency,
"has_Moderate_renal_insufficiency_(ClCr_=30-49_mL/min)" AS Moderate_renal_insufficiency,
has_None as None,
has_Other as Other,
"has_Parkinson's_disease" as Parkinson,
"has_Severe_renal_insufficiency_(ClCr_=_<30_mL/min)" as Severe_renal_insufficiency
FROM conn.xlsx
(txt, codepage is 28591, embedded labels, delimiter is ',', msq) WHERE "">=0 AND ""<=3

 

 

AUX1: Crosstable (Condition, [Has it])
Load Distinct
ID_,
COPD,
Cardiovascular_disease,
Diabetes ,
HIV,
Liver_dysfunction,
Mild_renal_insufficiency,
Moderate_renal_insufficiency,
None,
Other,
Parkinson,
Severe_renal_insufficiency
resident Orig
;

qualify *;
F:
load * resident AUX1
//WHERE [Has it] = 1
;
Unqualify *;
DROP TABLE AUX1;

 

 

Labels (1)
1 Solution

Accepted Solutions
Digvijay_Singh

Looks like you got some more qualifying columns in your table,  along with that it may be making sense to have both 0 and 1 value for one condition. Check out the third parameter in the crosstable function, you may like to have that. Otherwise its not making sense to me having patient 0 both yes and no for a single condition.

 

https://help.qlik.com/en-US/sense/June2020/Subsystems/Hub/Content/Sense_Hub/LoadData/work-with-cross...

 

View solution in original post

1 Reply
Digvijay_Singh

Looks like you got some more qualifying columns in your table,  along with that it may be making sense to have both 0 and 1 value for one condition. Check out the third parameter in the crosstable function, you may like to have that. Otherwise its not making sense to me having patient 0 both yes and no for a single condition.

 

https://help.qlik.com/en-US/sense/June2020/Subsystems/Hub/Content/Sense_Hub/LoadData/work-with-cross...