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!

[jessica_webb]

I end up with this:

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
68746	-	32	75	Expected	-	-
68746	-	32	75	Expected	-	-
2698	-	45	34	Expected	-	-
2698	-	45	34	Expected	-	-
968764	-	98	14	Expected	-	-
986764	-	98	14	Expected	-	-
21348	-	12	88	Expected	-	-
21348	-	12	88	Expected	-	-
9876	-	35	35	Expected	-	-
9876	-	35	35	Expected	-	-

[antoniotiman]

This is my app

Can You arrange Script according Your need ?

[jessica_webb]

I'm not managing so far!

Basically, I have many other fields that I've not listed here. I think that's the problem - it's not matching on the fields that aren't being listed in the concatenate field.

I tried:

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;

To try and load everything else as well, but then it gives me an error about having unique fields. Is there a way I can join just the new fields ('Expected' as Type and Expected as Value)?

[antoniotiman]

Type is an Old Field.

If Type must show 'Expected' then this is new Record, not field Joined to old Row.

So You need Concatenate instead of Join.

Assuming correct Table You are posting Concatenate should work.

Why not ?

[antoniotiman]

Hi Jessica,

try to Join instead of Concatenate, without any change.

Join

LOAD ID,

Set,

Target,

Current,

'Expected' as Type,

Max(Expected) as Value,

Max(Expected) as Expected

Resident Table

Group By ID,Set,Target,Current;

[jessica_webb]

Hi Antonio,

Still not working - the field 'ID' isn't in 'Table'.

This is my whole script, simplified:

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;

[antoniotiman]

Hi Jessica,

at the end of Your original script execution what is Your Data Model ?

You have only table TABLE. Which are the fields of TABLE ?

Can You share screenshot of table TABLE ?

[jessica_webb]

These are the final fields:

TABLE

Data_point

Name

ID

Group

Set_Group

Gender

Age

Set

Whole_Set

Initial

Prior_G

Band

Prior_K

Current

Target

Set_Current

Set_Target

Initial_Set

Type

Value

Hierarchy

Set_size

Value_difference

Current_points

Target_points

Set_pred

Subset

IsT

IsA

IsAD

IsAG

Prior

Prog

Nominal

Upper_expected

Upper

Lower

Lower_expected

Expected

CE

CST

CSU

CQ

CP

CD

CT

NE

NST

NSU

NQ

NP

ND

NT

[antoniotiman]

Jessica,

I'm sorry, I think should work.

[jessica_webb]

Maybe it would help if I explained what the purpose of this is? Perhaps I've overly complicated things!!

Basically, all I want at the end of it is one, single chart with Current, Target and Expected as the dimensions, and COUNT (ID) as the expression!

But the problem is, that whilst the three dimensions are separate, the values inside them are the same.

What I would like to see is:

But this doesn't seem possible in QV.

So what I was trying to do was have Type and Value as my dimensions...