Best practice - data transformation

[OysteinT]

Hi

I'm a bit rusty and looking for the best way to transform some data or do smart selections in a chart/table on a sheet.

I have a dataset which looks something like this:

PersonID	FieldName	StringValue	DateValue	DecimalValue	IntegerValue	ValueFrom	ValueTo
123	Age	NULL	NULL	NULL	40	2010-01-01	2019-12-31
123	Company	NULL	NULL	NULL	1	2010-01-01	NULL
123	Hours	NULL	NULL	37.5	NULL	2010-01-01	NULL
123	StartDate	NULL	2010-01-01	NULL	NULL	2010-01-01	NULL
123	City	Someplace	NULL	NULL	NULL	2010-01- 01	NULL
123	Age	NULL	NULL	NULL	50	2020-01-01	NULL
245	Age	NULL	NULL	NULL	66	2001-01-01	2020-12-31
245	City	Elsewhere	NULL	NULL	NULL	2020-01-01	2020-12-31

For each unique FieldName (I have 52 of them) I want to transform it into a new table and put in the corresponding non-NULL value and ValidTo/From.

AFAIK I have done something like this before, but am a bit rusty right now and just need a poke in the right direction. 

So I'd want something like this:
Table.PersonAge

PersonID	Age	ValidFrom	ValidTo
123	40	2010-01-01	2019-12-31
123	50	2020-01-01	NULL
245	66	2020-01-01	2020-31-12

Can I do something like this dynamically - or do I have to specify it for each Field I want?

[rwunderlich]

You can do this dynamically using the Generic Load prefix. 

https://help.qlik.com/en-US/sense/November2022/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPref...

-Rob

[OysteinT]

Hmmm

on paper that seems the solution - but with more than just 3 columns it doesn't work.

But I think I can just solve it visually in the sheet by using if(match) functions and multiple tables. 

[rwunderlich]

Consider this a further poke. The requirement for the From and To columns add a challenge. The key is to generate a RecId for each row so you can join the generic values back on to the row containing ValueFrom & ValueTo.

SET NullInterpret='NULL';
Data:
LOAD *, RecNo() as RecId Inline [
PersonID FieldName StringValue DateValue DecimalValue IntegerValue ValueFrom ValueTo
123 Age NULL NULL NULL 40 2010-01-01 2019-12-31
123 Company NULL NULL NULL 1 2010-01-01 NULL
123 Hours NULL NULL 37.5 NULL 2010-01-01 NULL
123 StartDate NULL 2010-01-01 NULL NULL 2010-01-01 NULL
123 City Someplace NULL NULL NULL 2010-01-01 NULL
123 Age NULL NULL NULL 50 2020-01-01 NULL
245 Age NULL NULL NULL 66 2001-01-01 2020-12-31
245 City Elsewhere NULL NULL NULL 2020-01-01 2020-12-31
] (delimiter is '\t');

Fields:
Generic
Load RecId,
  FieldName,
  Coalesce(StringValue, DateValue, DecimalValue, IntegerValue)
Resident Data
;

For i = NoOfTables() to 0 step -1
  Let vTable = TableName($(i));
  IF '$(vTable)' like 'Fields.*' THEN
    Join (Data) Load * Resident $(vTable);
    Drop Table $(vTable);
  ENDIF
Next i

Drop Fields StringValue, DateValue, DecimalValue, IntegerValue
  ,FieldName, RecId; // Optional drop

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com