Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
sylvain_piccarreta
Contributor III
Contributor III

group a table with some rows with null in columns

Hi,

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

ObjectValue1Value2Value3
Agreen--
A-Nature-
A--25
Bblue--
B-Mechanical-
B--35

 

I would like to have a table 

ObjectValue1Value2Value3
AgreenNature25
BblueMechanical35

 

How to do it please ?

 

Thanks for your help,

 

Sylvain

3 Replies
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
Contributor III
Contributor III
Author

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