Crosstable is duplicating binary fields

EugeniaCastilla — Thu, 21 Jul 2022 23:05:08 GMT

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:

Create a crosstable
Filter that crosstable to value=1
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:

What my cross table is showing for patient 0:

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;

Re: Crosstable is duplicating binary fields

Digvijay_Singh — Fri, 22 Jul 2022 01:14:50 GMT

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-tables.htm

topic Re: Crosstable is duplicating binary fields in App Development

Crosstable is duplicating binary fields

Re: Crosstable is duplicating binary fields