group a table with some rows with null in columns

[sylvain_piccarreta]

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

[Kushal_Chawda]

@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

[sylvain_piccarreta]

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

[Kushal_Chawda]

@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