Load only fields with values

[beck_bakytbek]

Hi Folks,

i got a issue: i am extracting tables from SAP-Area and some tables have a lot empty fields, for instance:

TableNr1:

Field1, Field2, Field3, Field4, Field5, Field6, Field7

A,  0, 0, A, A, 0, A

As you see, i have in this example 7 Columns, where i have only 4 Columns with Values, and Other Columns are empty.

My Question is, how can i load only Columns with Values, in this example: only Field1, Field4, Field5, Field7.

Of Course my SAP-Tables consist of a lot Columns. is there a smart solution to resolve this issue?

Thanks a lot for your help and time

Beck

[Chanty4u]

Hi

can you here sample.

https://community.qlik.com/t5/New-to-QlikView/Loading-only-Field-Names-with-no-data/td-p/311020

[beck_bakytbek]

Hi Chanty4u,

thank you for your help and time, i tried to implement your Suggestion, but it doesn't work

[JordyWegman · Accepted solution]

Hi Beck,

This is what you need. It will both check fields with values and strings.

Table:
Load * Inline [
Customer, Year, Amount, Rev, test, bla,
A, 2019, 1000000, Revenue
A, 2018, 100000, License
A, 2017, 600000, Revenue
B, 2019, 450000, Revenue
B, 2019, 40000, License
B, 2018, 800000, Revenue
C, 2019, 78000, Revenue
C, 2019, 8000, License
C, 2018, 76000, Revenue
D, 2019, 780000, Revenue
D, 2019, 97000, License
D, 2018, 370000, Revenue
E, 2018, 758000, Bla
E, 2018, 45000, Bla
E, 2017, 780000, Etc
];

let vTest = 0;

set errormode = 0;

// Check each field in the table
FOR i = 1 to NoOfFields('Table')

	// Check if you are on the first row
	let vTest = $(vTest) +1;

	// If so, set the first variable. This is needed while otherwise it will overwrite the variable.
	IF $(vTest) = 1 THEN
    
    	set vNames = '1';
    
    ENDIF

	// Pick the first column
    Fields:
    LOAD
      FieldName($(i),'Table') as FieldName
    AutoGenerate 1
    ;

	// Store this in a variable
    let vFieldName = peek('FieldName',0,'Fields');
	
    // Pick the max value
	NoConcatenate
    Final:
    LOAD
      Max($(vFieldName)) as $(vFieldName),
      MaxString($(vFieldName)) as $(vFieldName)2
    Resident Table
    ;

	// Store this in a variable. Check if both values are zero
    let vNoOfRows = IF( Trim(Peek('$(vFieldName)',0,'Final')) & '' = ''
					AND Trim(Peek('$(vFieldName)2',0,'Final')) & '' = '','NoValue',Peek('$(vFieldName)',0,'Final'));

	TRACE NoOfRows: $(vNoOfRows);

	// If it's not empty then we want the row
    IF '$(vNoOfRows)' <> 'NoValue' then

		// This will concatenate all field names
      set vNames = $(vNames),$(vFieldName);

    ENDIF

    TRACE Name of fieldname: $(vFieldName);

    TRACE Concatenate string: $(vNames);

	// Drop all because otherwise it will auto concatenate
    Drop tables Fields, Final;

NEXT i

// Create the load script
let vLoadScript = Right('$(vNames)',Len('$(vNames)')-2);

TRACE Load script: $(vLoadScript);


Facts:
Load
$(vLoadScript)
Resident Table;

Drop table Table;

Jordy

Climber

[beck_bakytbek]

Hi JordyWegman, thanks a lot for your help and your time