Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
You can do this dynamically using the Generic Load prefix.
-Rob
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.
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