Reload data with renamed fields

[jessica_webb]

I have a table as follows, created through many joins of different data sources:

ID	Set	Target	Current	Type	Value	Expected
68746	A	32	75	Target	32	65
68746	A	32	75	Current	75	65
2698	A	45	34	Target	45	12
2698	A	45	34	Current	34	12
968764	B	98	14	Target	98	32
968764	B	98	14	Current	14	32
21348	C	12	88	Target	12	78
21348	C	12	88	Current	88	78
9876	B	35	35	Target	35	61
9876	B	35	35	Current	35	61

What I would like now, is to keep the 'Expected' field, but reload it and add the data to the 'Type' and 'Value' fields

So for each ID and Set, I would have a third row with Type as 'Expected' and the corresponding value. Example:

ID	Set	Target	Current	Type	Value	Expected
68746	A	32	75	Target	32	65
68746	A	32	75	Current	75	65
68746	A	32	75	Expected	65	65

The 'Expected' column comes from a Left Join:

Left Join (TABLE)

IntervalMatch(Points, Set)

LOAD Lower_points,

  Expected,

  Set

Resident EXPECTED_TABLE;

So I tried adding:

Left Join (TABLE)

IntervalMatch(Points, Set)

LOAD Lower_points,

  Expected,

  Set,

  'Expected' as Type,

  Expected as Value

Resident EXPECTED_TABLE;

But that did nothing! I didn't get any errors, but also, my data didn't change!

[PrashantSangle]

can you post your entire script.

Regards

[jessica_webb]

Not really, it spans 7 tabs.

Can I give you any other information that might help?

[PrashantSangle]

is it possible to provide interval match script and table on which you are doing left join.

Regards,

[techvarun]

Please try this

Master:

LOAD * INLINE [

    ID, Set, Target, Current, Type, Value

    68746, A, 32, 75, Target, 32

    68746, A, 32, 75, Current, 75

    2698, A, 45, 34, Target, 45

    2698, A, 45, 34, Current, 34

    968764, B, 98, 14, Target, 98

    968764, B, 98, 14, Current, 14

    21348, C, 12, 88, Target, 12

    21348, C, 12, 88, Current, 88

    9876, B, 35, 35, Target, 35

    9876, B, 35, 35, Current, 35

];

LJ:LOAD * INLINE [

    ID, Set, Target, Current, Type, Value, Expected

    68746, A, 32, 75, Expected, 65, 65

    2698, A, 45, 34, Expected, 12, 12

    968764, B, 98, 14, Expected, 32, 32

    21348, C, 12, 88, Expected, 78, 78

    9876, B, 35, 35, Expected, 61, 61

];

Concatenate(Master) Load * Resident LJ;

DROP Table LJ;

[antoniotiman]

Hi Jessica,

try this

Table:
LOAD ID,
Set,
Target,
Current,
Type,
Value,
Expected
FROM
"https://community.qlik.com/message/1310547"
(html, codepage is 1252, embedded labels, table is @1);
Concatenate
LOAD ID,
Set,
Target,
Current,
'Expected' as Type,
Max(Expected) as Value,
Max(Expected) as Expected
Resident Table
Group By ID,Set,Target,Current; 

Regards,

Antonio

[jessica_webb]

TABLE:

LOAD

ID,

ApplyMap('Short_map',Whole_Set)) as Set,

Current,

Target,

Expected_points

FROM

[..\..\..\..\..\*****\Data.xlsx]

(ooxml, embedded labels, table is [Data]);

temp1:

CrossTable(Type, Value, 3)

LOAD

ID,

ApplyMap('Short_map',Whole_Set)) as Set,

Current,

Target

FROM

[..\..\..\..\..\*****\Data.xlsx]

(ooxml, embedded labels, table is [Data]);

LEFT JOIN (temp1)

LOAD

Set,

Value,

Order

FROM

[..\..\..\..\..\*****\Values.xlsx]

(ooxml, embedded labels, table is [Values]);

LEFT JOIN (TABLE) LOAD *

Resident temp1;

Drop TABLE temp1;

//Add expected

EXPECTED_TABLE_TEMP:

LOAD Set,

     Points,

     First_Value as Upper_Value

FROM

[..\..\..\..\..\*****\Points.xlsx]

(ooxml, embedded labels, table is [Points]);

EXPECTED_TABLE:

LOAD Set,

Points,

Upper_Value,

  Points as Upper_points,

  If(Set= Previous(Set), Alt(Previous(Points) + 0.01, 0), 0) as Lower_points,

  If(Set= Previous(Set), Alt(Previous(Upper_Value)), 0) as Expected

Resident EXPECTED_TABLE_TEMP

Order By Set, Points;

DROP Table EXPECTED_TABLE_TEMP;

Left Join (TABLE)

IntervalMatch(Expected_points, Set)

LOAD Lower_points,

   Set

Resident EXPECTED_TABLE;

Left Join (TABLE)

LOAD *

Resident EXPECTED_TABLE;

DROP Table EXPECTED_TABLE;

[jessica_webb]

Hi,

Problem is, I don't have the second table to load - I'm trying to create it!

Thanks for the suggestion though.

[jessica_webb]

Thanks Antonio!

Unfortunately it's not working quite right - it isn't concatenating properly (I expect something to do with not joining on all the correct fields, but I'm not sure which other fields to include!)

[antoniotiman]

Which rows are wrong ?