JSON delimiting from column in QVD

[alextomlins]

Hi Guys,

I'm trying to transform a column loaded in from a SQL Table called 'Data' that is JSON format. The below is erroring. I might be trying to do totally the wrong thing but am hoping someone can help me start cutting this string up into new columns. I am trying to do the following;

JSONTable:

Load e.[EngagementID],

Key1,

Key2;

SQL SELECT

e.[EngagementID] as 'EngagementID',

Key1=JSON_VALUE(Data,'$.key1'),

Key2=JSON_VALUE(Data,'$.key2')

FROM [LoremIpsum].[dbo].[LoremIpsum] e with (nolock)

INNER JOIN [LoremIpsum].[dbo].[LoremIpsum] et

on e.[EngagementTypeID] = et.[EngagementTypeID];

        Exit Script;

[rittermd]

What error are you getting?

[petter]

You must refer to the aliased columnname which is EngagementID and not the original columnname e.[EngagemendID].

So try this:

LOAD

  EngagementID,

  Key1,

  Key2;

SQL .....

.....

;

[rittermd]

Good catch.  Totally missed that.

[alextomlins]

That has really helped. I have found however that we are on a really old SQL server and as a result JSON_VALUE function does not work   This has really ruffled me!

As we have the data column in is there anyway to just delimit it as you could in excel and then remove the title and just have the value?

I hope so hahaah

Thanks,

Alex

[alextomlins]

Hi Mark - see below - it's to do with the JSON_VALUE and our version of SQL server. Is there any other way of delimiting this column to multiple columns. They are all split via a ',' i believe. 

Thanks so much eitherway

Thanks,

Alex

[alextomlins]

Hi Petter. Thanks so much and this definitely needs fixing but the issue i have i've replied to this thread about. It's to do with SQL Server and JSON_Value function

I'd love to know if there's any other qlik solutions i can use here? Like delimiting by ',' or something?

Thanks eitherway

Thanks,

Alex