topic Re: Inline Query in QlikView

Inline Query

Anonymous — Sun, 27 Jul 2014 19:15:22 GMT

Hi Team,

I have the Inline table like the one below.

inline [
From,To,Match

230,235,10100
236,239,10101
];

Instead of giving the values 230,235,10100 etc.. is there a way I can import the data from sources.

If you could you please share the syntax.

Thanks & Regards

Jeba

Re: Inline Query

its_anandrjs — Sun, 27 Jul 2014 19:52:28 GMT

Which type of source it is let me know.

Re: Inline Query

Anonymous — Sun, 27 Jul 2014 19:59:36 GMT

Hi Anand,

Thanks for the reply.

I want to load From,To,Match from the qvd

I would like to give you more info about my requirement.I have two table one has info like from,To and match

These all are Zip code ranges and in my second table I have zipcodes.I am trying to allocate the "Match" field to my second table if the zip code falls between From and to.If you jave anyother easy way kindly let me know

Kindly help.

Thanks & Regards

Jeba

Re: Inline Query

its_anandrjs — Sun, 27 Jul 2014 20:11:13 GMT

As i understand you have to use the joins for that and get the right table, if you explain this with an example will be good.

Re: Re: Inline Query

Anonymous — Sun, 27 Jul 2014 20:20:40 GMT

Hi Anand,

Sorry for the inconvenience,I am attaching the sample data.

Table 1:

I have 3 digit code ranges from and to with my ware house code(Match)

Table 2:

Here I have actual zip codes.I want to map my warehouse codes based on the ranges to second table.

It also has alphanumerics.Because Canadian zip codes are alphanumerics

Kindly help me.

Thanks & Regards

Jeba

Re: Re: Inline Query

its_anandrjs — Sun, 27 Jul 2014 20:52:10 GMT

Try to load your table like this way and the data that you provide it not seems good for understand may be it is sample so for understanding i added 2 rows with values

23519, 10100

24005, 10101

But please change according to your data or may be provide complete data and please check the load script

Table2:

LOAD [Zip Codes], Match, Left([Zip Codes],3) as ZipCode;

LOAD * INLINE [

Zip Codes, Match

23011, 10100

23012, 10100

23013, 10100

23014, 10100

23015, 10100

23016, 10100

23017, 10100

23018, 10100

23519, 10100

23600, 10101

23601, 10101

23602, 10101

23603, 10101

23604, 10101

24005, 10101

A0A, 10123

A0B, 10123

A1B, 10123

A1D, 10123

Q0A, 10124

Q0B, 10124

Q1A, 10124

Q1C, 10124

];

NewTable:

LOAD

Match,

MinString(ZipCode) as [Zip Code_From],

MaxString(ZipCode) as [Zip Code_To]

Resident Table2

Group By Match;

And you get table as

Re: Inline Query

Anonymous — Sun, 27 Jul 2014 20:58:59 GMT

Hi Anand,

The sample two rows you have added are correct.That matches my logic.

Just one question.

How do I load the Inline statement from my qvd file.I don't want to load the static codes.

If you could help me that then we are done with this logic.

Kindly help

Thanks & Regards

Jeba

Re: Inline Query

its_anandrjs — Sun, 27 Jul 2014 21:04:41 GMT

But why you want to read data from QVD as inline but it is directly available i believe no need from that.

Re: Inline Query

Anonymous — Sun, 27 Jul 2014 21:07:05 GMT

Hi Anand,

The data I gave you is some sample rows,I have lot of ranges like that so it will be very difficult to add the rows also if they add any new codes it should accomodate.

Kindly help me

Thanks & Regards

Jeba

Re: Inline Query

SunilChauhan — Mon, 28 Jul 2014 04:52:55 GMT

simply in place of t Table2 you could use load from qvd

Table2:

LOAD [Zip Codes], Match, Left([Zip Codes],3) as ZipCode;

LOAD Match,

[ZIP Codes]

from Table2.qvd

LOAD

Match,

MinString(ZipCode) as [Zip Code_From],

MaxString(ZipCode) as [Zip Code_To]

Resident Table2

Group By Match;

Re: Inline Query

its_anandrjs — Mon, 28 Jul 2014 04:59:03 GMT

As i understand by our base table that is Table2 then in place of the inline use QVD and please elaorate more about that we are so much near to the solution

Table2:

LOAD

[Zip Codes],

Match,

Left([Zip Codes],3) as ZipCode

From Table2.qvd(qvd);

NewTable:

LOAD

Match,

MinString(ZipCode) as [Zip Code_From],

MaxString(ZipCode) as [Zip Code_To]

Resident Table2

Group By Match;

Let me know about this.

Re: Re: Inline Query

Anonymous — Mon, 28 Jul 2014 13:35:49 GMT

Hi Anand

Thanks for the help.

I tried the logic,it seems some where I am missing the logic.

I am attaching the qvw file and the data I have used.

Note:Base tab will not have "Match" field.That is the one I am trying to map.

Could you please help me.

Thanks & Regards

Jeba

Re: Inline Query

Anonymous — Mon, 28 Jul 2014 18:57:38 GMT

Hi Anand,

Did you had a chance to look at my message.

Could you please help me.

Thanks & Regards

Jeba

Re: Inline Query

Anonymous — Fri, 01 Aug 2014 07:03:06 GMT

Hi Anand,

Did you had a chance to look at my message.

Could you please help me.

Thanks & Regards

Jeba