topic Re: Reload data with renamed fields in QlikView

Reload data with renamed fields

jessica_webb — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Reload data with renamed fields

PrashantSangle — Thu, 20 Jul 2017 10:00:06 GMT

can you post your entire script.

Regards

Re: Reload data with renamed fields

jessica_webb — Thu, 20 Jul 2017 10:03:36 GMT

Not really, it spans 7 tabs.

Can I give you any other information that might help?

Re: Reload data with renamed fields

PrashantSangle — Thu, 20 Jul 2017 10:07:36 GMT

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

Regards,

Re: Reload data with renamed fields

techvarun — Thu, 20 Jul 2017 10:13:02 GMT

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;

Re: Reload data with renamed fields

antoniotiman — Thu, 20 Jul 2017 10:15:41 GMT

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

Re: Reload data with renamed fields

jessica_webb — Thu, 20 Jul 2017 10:21:42 GMT

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;

Re: Reload data with renamed fields

jessica_webb — Thu, 20 Jul 2017 10:29:52 GMT

Hi,

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

Thanks for the suggestion though.

Re: Reload data with renamed fields

jessica_webb — Thu, 20 Jul 2017 10:33:15 GMT

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!)

Re: Reload data with renamed fields

antoniotiman — Thu, 20 Jul 2017 10:38:33 GMT

Which rows are wrong ?

Re: Reload data with renamed fields

jessica_webb — Thu, 20 Jul 2017 10:46:24 GMT

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	-	-

Re: Reload data with renamed fields

antoniotiman — Thu, 20 Jul 2017 11:05:16 GMT

This is my app

Can You arrange Script according Your need ?

Re: Reload data with renamed fields

jessica_webb — Thu, 20 Jul 2017 14:31:28 GMT

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)?

Re: Reload data with renamed fields

antoniotiman — Thu, 20 Jul 2017 14:53:36 GMT

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 ?

Re: Reload data with renamed fields

antoniotiman — Thu, 20 Jul 2017 16:53:41 GMT

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;

Re: Reload data with renamed fields

jessica_webb — Fri, 21 Jul 2017 06:39:27 GMT

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;

Re: Reload data with renamed fields

antoniotiman — Fri, 21 Jul 2017 06:54:48 GMT

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 ?

Re: Reload data with renamed fields

jessica_webb — Fri, 21 Jul 2017 07:07:22 GMT

These are the final fields:

TABLE

Data_point

Name

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

CST

CSU

NST

NSU

Re: Reload data with renamed fields

antoniotiman — Fri, 21 Jul 2017 07:23:20 GMT

Jessica,

I'm sorry, I think should work.

Re: Reload data with renamed fields

jessica_webb — Fri, 21 Jul 2017 07:23:57 GMT

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...