topic group a table with some rows with null in columns in QlikView

group a table with some rows with null in columns

sylvain_piccarreta — Fri, 16 Jul 2021 08:41:22 GMT

Hi,

I have a QlikView with some rules in the script to provide a table with some nulls in fields. like below

Object	Value1	Value2	Value3
A	green	-	-
A	-	Nature	-
A	-	-	25
B	blue	-	-
B	-	Mechanical	-
B	-	-	35

I would like to have a table

Object	Value1	Value2	Value3
A	green	Nature	25
B	blue	Mechanical	35

How to do it please ?

Thanks for your help,

Sylvain

Re: group a table with some rows with null in columns

Kushal_Chawda — Fri, 16 Jul 2021 11:02:42 GMT

@sylvain_piccarreta try below

Data:
CrossTable(Data,Value)
LOAD Object,
Value1,
Value2,
Value3
FROM Table;

Final:
Generic LOAD Object,
Data,
Value
Resident Data
where len(trim(Value))>0;

DROP Table Data;

Obejcts:
LOAD FieldValue('Object',RecNo()) as Object
AutoGenerate FieldValueCount('Object');

FOR i = NoOfTables()-1 to 0 STEP -1
LET vTable=TableName($(i));
IF WildMatch('$(vTable)', 'Final.*') THEN
LEFT JOIN (Obejcts) LOAD * RESIDENT [$(vTable)];
DROP TABLE [$(vTable)];
ENDIF
NEXT i

Re: group a table with some rows with null in columns

sylvain_piccarreta — Mon, 19 Jul 2021 10:04:08 GMT

It doesn't work. I try to adapt your script and I have the script below :

temp1:
generic load * resident temp_5
where len(trim(ID_Field))>2 and len(trim(ID_Field))<7
;

result:
load distinct ID resident temp_5
where len(trim(ID_Field))>2 and len(trim(ID_Field))<7
;

FOR i = 0 to NoOfTables()
TableList:
LOAD TableName($(i)) as Tablename AUTOGENERATE 1
WHERE WildMatch(TableName($(i)), 'Final.*');
NEXT i

FOR i = 1 to FieldValueCount('Tablename')
LET vTable = Replace(FieldValue('Tablename', $(i)),' ','');
//LOAD * resident result;
//LEFT JOIN LOAD * RESIDENT $(vTable);
LEFT JOIN ([result]) LOAD * RESIDENT $(vTable);
DROP TABLE $(vTable);
NEXT i

drop table TableList;

But, it doesn't work again.

Thanks for your help

Re: group a table with some rows with null in columns

Kushal_Chawda — Mon, 19 Jul 2021 10:09:11 GMT

@sylvain_piccarreta You are not following the script which I have provided. In your script first step itself is missing where cross table load is applied. Also last step is wrong. Please follow my script exactly as is