Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Which type of source it is let me know.
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
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.
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
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
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
But why you want to read data from QVD as inline but it is directly available i believe no need from that.
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
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;