Inline Query

[Anonymous]

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

[its_anandrjs]

Which type of source it is let me know.

[Anonymous]

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

[its_anandrjs]

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.

[Anonymous]

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

[its_anandrjs · Accepted solution]

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

[Anonymous]

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

[its_anandrjs]

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

[Anonymous]

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

[SunilChauhan]

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;